即使我们并没有数据，如何选择当年每个月的数据总和

Question

我有一个SQL表：

CREATE TABLE test (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date NULL,
`distance` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO test (date, distance) VALUES ('2020-01-01','1');
INSERT INTO test (date, distance) VALUES ('2020-01-15','3');
INSERT INTO test (date, distance) VALUES ('2020-02-10','1');
INSERT INTO test (date, distance) VALUES ('2020-02-20','4');
INSERT INTO test (date, distance) VALUES ('2020-05-06','8');
INSERT INTO test (date, distance) VALUES ('2020-11-12','2');
INSERT INTO test (date, distance) VALUES ('2020-11-13','5');

我想做的是从今年开始每个月都有一笔钱，但是，即使我没有这个月的数据，保持零和的月份有什么重要的

这是小提琴：https://www.db-fiddle.com/f/9KmGk6uxBQxSRxr5Qsid3S/2

我试过这样的东西：

SELECT YEAR_MONTH(`date`) as date, SUM(distance) FROM test GROUP BY `date`;

Answer 1

基本上，您需要一个包含一年中月份的日历表，然后可以使用该表left join。

您可以手动创建表，也可以使用递归查询(需要MySQL 8.0(动态生成表：

with cte as (
select date_format(current_date, '%Y-01-01') as dt
union all select dt + interval 1 month from cte where dt < date_format(current_date, '%Y-01-01')  + interval 11 month
)
select c.dt, coalesce(sum(t.distance), 0) as sum_distance
from cte c
left join test t 
on  t.date >= c.dt
and t.date <  c.dt + interval 1 month
group by c.dt
order by c.dt