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

Question

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 个月的销售额。

输出:

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

Answer 1

考虑下表

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 和 dec 2014 如果我们需要单独的计算它们然后我们可以使用以下技术生成动态日期范围 MySql Single Table, Select last 7 days and include empty rows

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)

如果您不关心上述情况，即 2014 年 12 月和 2013 年 12 月

然后只需将动态日期部分中的group by更改为

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

最后分组依据为 group by t1.month