仅限今年的 MySQL（now() - 间隔 1 个月）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39462114/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL (now() - Interval 1 Month) for this year only
提问by stevenbranigan82
I need some help with this.
我需要一些帮助。
I have the following SQL statement
我有以下 SQL 语句
SELECT * FROM table
WHERE MONTH(ORDERDATE) = MONTH(NOW() - INTERVAL 1 MONTH)
The problem is that it is returning data from last month but for all years... Am I doing something wrong or is this a normal issue that people face with this function?
问题是它正在返回上个月的数据，但多年来......我做错了什么还是人们在使用此功能时遇到的正常问题？
The problem I am facing is that if I use the YEAR(NOW()) the report I am writing will not show the data for 2016 when we hit 2017. I'm trying to write a 6 month sales history report.
我面临的问题是，如果我使用 YEAR(NOW())，当我们到达 2017 年时，我正在编写的报告将不会显示 2016 年的数据。我正在尝试编写一份 6 个月的销售历史报告。
Any guidance would be appreciated.
任何指导将不胜感激。
Added Information
添加信息
SELECT * FROM DATA_WH.SALESORD_HDR WHERE MONTH(ORDERDATE) = MONTH(NOW() - INTERVAL 1 MONTH)
RETURNS....
退货....
'2015-08-14 00:00:00'
'2015-08-14 00:00:00'
回答by Tim Biegeleisen
Try using DATE_SUBwith BETWEEN:
尝试使用DATE_SUB带BETWEEN：
SELECT *
FROM DATA_WH.SALESORD_HDR
WHERE ORDERDATE BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND
DATE_SUB(NOW(), INTERVAL 2 MONTH)
This avoids the problem of having to deal with boundary conditions when using MONTHand YEAR.
这避免了在使用MONTH和时必须处理边界条件的问题YEAR。
Edit:
编辑：
The above query will return records whose order date is between one and two months old. If you want to identify orders from the previous calendar month, then you will have to do a bit more work. Try this query:
上面的查询将返回其订单的日期是几个月一到两个记录老。如果您想识别上一个日历月的订单，那么您将需要做更多的工作。试试这个查询：
SELECT *
FROM DATA_WH.SALESORD_HDR
WHERE ORDERDATE >= STR_TO_DATE(CONCAT('01-', LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 2, '0'), '-', YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH))), '%d-%m-%Y') AND
ORDERDATE < STR_TO_DATE(CONCAT('01-', LPAD(MONTH(NOW()), 2, '0'), '-', YEAR(NOW())), '%d-%m-%Y')
The strategy here is to build the date boundaries (August 1 and September 1 of 2016, as of the time of writing this answer), using the ORDERDATE.
这里的策略是使用ORDERDATE.
Here is a Fiddle showing this logic in action:
这是一个显示此逻辑的小提琴：
SQLFiddle
SQLFiddle
回答by Haresh Vidja
I think you have to add another condition with AND with YEAR function
我认为您必须使用 AND 和 YEAR 函数添加另一个条件
SELECT * FROM DATA_WH.SALESORD_HDR WHERE MONTH(ORDERDATE) = MONTH(NOW() - INTERVAL 1 MONTH) AND YEAR(ORDERDATE)= YEAR(NOW());
