MySQL MySQL查询计算上个月
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1138190/
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 Query to calculate the Previous Month
提问by Ibn Saeed
I would like to calculate total order amount in the previous month.
我想计算上个月的总订单金额。
I got the query for getting the data for the present month from the current date.
我得到了从当前日期获取当月数据的查询。
SELECT SUM(goods_total) AS Total_Amount FROM orders
WHERE order_placed_date >= date_sub(current_date, INTERVAL 1 MONTH);
Now how can I get Previous Months Data only, excluding this month.
现在我怎样才能获得前几个月的数据，不包括这个月。
For e.g. This month (July) I made $15,000 and last Month(June) i made $14,000.
例如，本月（7 月）我赚了 15,000 美元，上个月（6 月）我赚了 14,000 美元。
I get the $15,000 by running the above query.
通过运行上述查询，我获得了 15,000 美元。
But i dont know how to calculate Previous Months.
但我不知道如何计算前几个月。
回答by Artem Russakovskii
Here you go, use this to get the date between the 1st of last month and the last of last month in MySQL:
在这里，使用它来获取 MySQL 中上个月 1 日和上个月最后一个月之间的日期：
... order_placed_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')
AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
回答by Shane
I found that Artem's query wasn't returning anything from the last day of the previous month(presumably as BETWEEN would calculate from time 00:00:00 of the last day).
我发现 Artem 的查询没有从上个月的最后一天返回任何内容（大概是因为 BETWEEN 会从最后一天的 00:00:00 时间开始计算）。
This seems to work for me for the previous month.
这似乎对我上个月有用。
order_placed_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
AND DATE_FORMAT(NOW() ,'%Y-%m-01')
回答by BigBadMe
Simplest solution:
最简单的解决方案：
SELECT SUM(goods_total) AS Total_Amount FROM orders
WHERE YEAR(order_placed_date) = YEAR(CURDATE() - INTERVAL 1 MONTH)
AND MONTH(order_placed_date) = MONTH(CURDATE() - INTERVAL 1 MONTH)
回答by fubo
here another solution - without stringoperation
这里是另一种解决方案 - 没有字符串操作
SELECT SUM(goods_total) AS Total_Amount FROM orders
WHERE DATE(order_placed_date)
BETWEEN DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH));
回答by fubo
If you are lazy it's kinda convenient to use
如果你很懒，使用起来还算方便
...date_format(order_placed_date, '%Y-%m') = date_format(now() - INTERVAL 1 MONTH, '%Y-%m')
...date_format(order_placed_date, '%Y-%m') = date_format(now() - INTERVAL 1 MONTH, '%Y-%m')
I think it also increases readability.
我认为这也增加了可读性。
回答by Ibn Saeed
Here is another way i found:
这是我发现的另一种方法：
SELECT SUM(goods_total) AS Total_Amount FROM orders
WHERE SUBSTRING(o.order_placed_date FROM 1 FOR 7) = SUBSTRING(CURRENT_DATE - INTERVAL 1 MONTH FROM 1 FOR 7)
This works as well.
这也有效。
