MySQL 查询按日期范围分组

Question

我在执行此查询时遇到问题:

SELECT
    YEAR(passDate) as Year,
    count(*) AS total_amount
FROM 
    `dsp_drop_pass_details`
WHERE passDate
    BETWEEN '2009-01-01'
    AND '2012-05-01'
    AND batch='DROPOUT'
GROUP BY YEAR(passDate)
ORDER BY YEAR(passDate)

输出---

Month      total amount
2011             30
2012             20

但我的愿望是

Month    total amount 
2009           0
2010           0
2011           40
2012           20

我想要 0，其中计数为 0

Answer 1

最简单的方法是有一个“年”表，其中包含所有想要的年份。

create table Years(yearValue int);

insert into years values (2001), (2002)..., (2020);

然后在您的实际表上执行左连接。

SELECT y.yearValue, COALESCE(COUNT(d.id), 0) --assumning you have a field id in dsp_drop_pass_details
FROM years y
LEFT join dsp_drop_pass_details d on y.yearValue = YEAR(d.passDate)
WHERE y.yearValue between 2009 and 2012
GROUP BY y.yearValue
ORDER BY y.yearValue;