MySQL 过去 12 个月的月度销售情况，包括没有销售的月份

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")

此查询仅显示现有月份的结果。我需要所有 12 个月的销售额。

Output:

"month" "total"  
--------------  
"Jun"   "22"
"Aug"   "30"
"Oct"   "19"
"Nov"   "123"
"Dec"   "410"

所需输出:

"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"

考虑下表

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 |
+------+------------+-------------+

现在按照逻辑你回顾一年december将在结果中出现两次，即dec 2013 and dec 2014如果我们需要对它们进行单独的计数，那么我们可以使用以下生成动态日期范围的技术MySql 单表，选择最近 7 天并包含空行 https://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236

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
;

输出将是

+-------+---------+-------+
| 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)

如果您不关心上述情况，即dec 2014 and dec 2013

然后只需更改group by在动态日期部分为

where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
  group by month

和最终分组为group by t1.month