MySQL最近12个月的月度销售，包括无月销售?mysql

考虑下表

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天并包含空行

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来源：stack overflow