MySQL 过去 12 个月的每月销售，包括没有销售的月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27600863/
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 monthly Sale of last 12 months including months with no Sale
提问by Wasim A.
SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM cart
WHERE date <= NOW()
and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")
This query displaying result for only existing month. I need all 12 months sales.
此查询仅显示现有月份的结果。我需要所有 12 个月的销售额。
Output:
输出：
"month" "total"
--------------
"Jun" "22"
"Aug" "30"
"Oct" "19"
"Nov" "123"
"Dec" "410"
Required Output:
所需输出：
"month" "total"
--------------
"Jan" "0"
"Feb" "0"
"Mar" "0"
"Apr" "0"
"May" "0"
"Jun" "22"
"Jul" "0"
"Aug" "30"
"Sep" "0"
"Oct" "19"
"Nov" "123"
"Dec" "410"
采纳答案by Wasim A.
Thanks for @pankaj hint, Here i resolved it via this query...
感谢@pankaj 提示，在这里我通过这个查询解决了它...
SELECT
SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
SUM(IF(month = 'May', total, 0)) AS 'May',
SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
SUM(IF(month = 'Dec', total, 0)) AS 'Dec',
SUM(total) AS total_yearly
FROM (
SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM cart
WHERE date <= NOW() and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")) as sub
回答by Abhik Chakraborty
Consider the following table
考虑下表
mysql> select * from cart ;
+------+------------+-------------+
| id | date | total_price |
+------+------------+-------------+
| 1 | 2014-01-01 | 10 |
| 2 | 2014-01-20 | 20 |
| 3 | 2014-02-03 | 30 |
| 4 | 2014-02-28 | 40 |
| 5 | 2014-06-01 | 50 |
| 6 | 2014-06-13 | 24 |
| 7 | 2014-12-12 | 45 |
| 8 | 2014-12-18 | 10 |
+------+------------+-------------+
Now as per the logic you are looking back one year and decemberwill appear twice in the result i.e. dec 2013 and dec 2014and if we need to have a separate count for them then we can use the following technique of generating dynamic date range MySql Single Table, Select last 7 days and include empty rows
现在根据逻辑，您回顾一年，结果december将出现两次，即dec 2013 and dec 2014如果我们需要对它们进行单独计数，那么我们可以使用以下技术生成动态日期范围MySql 单表，选择过去 7 天并包括空行
t1.month,
t1.md,
coalesce(SUM(t1.amount+t2.amount), 0) AS total
from
(
select DATE_FORMAT(a.Date,"%b") as month,
DATE_FORMAT(a.Date, "%m-%Y") as md,
'0' as amount
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
group by md
)t1
left join
(
SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as amount ,DATE_FORMAT(date, "%m-%Y") as md
FROM cart
where Date <= NOW() and Date >= Date_add(Now(),interval - 12 month)
GROUP BY md
)t2
on t2.md = t1.md
group by t1.md
order by t1.md
;
Output will be
输出将是
+-------+---------+-------+
| month | md | total |
+-------+---------+-------+
| Jan | 01-2014 | 30 |
| Feb | 02-2014 | 70 |
| Mar | 03-2014 | 0 |
| Apr | 04-2014 | 0 |
| May | 05-2014 | 0 |
| Jun | 06-2014 | 74 |
| Jul | 07-2014 | 0 |
| Aug | 08-2014 | 0 |
| Sep | 09-2014 | 0 |
| Oct | 10-2014 | 0 |
| Nov | 11-2014 | 0 |
| Dec | 12-2013 | 0 |
| Dec | 12-2014 | 55 |
+-------+---------+-------+
13 rows in set (0.00 sec)
And if you do not care about the above case i.e. dec 2014 and dec 2013
如果你不关心上述情况，即 dec 2014 and dec 2013
Then just change the group byin dynamic date part as
然后只需将group by动态日期部分更改为
where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
group by month
and final group by as group by t1.month
最后分组为 group by t1.month
