MySql SUM()，按月分组，带 '0'的月份不出现

Question

我有这个 MySql 查询:

SELECT YEAR(date), MONTHNAME(date), SUM(block_time)
FROM flights
WHERE operator = 'PSD'
GROUP BY YEAR(date), MONTH(date)

我遇到的问题是，如果一个月的小时数为零，则它不会出现在结果中。我需要更改什么才能查看所有月份是否有该月的飞行时间？

Answer 1

您需要一个表格，其中包含您想要在结果中显示的所有年份/月份，并且需要按如下方式加入它...

SELECT myMonths.theYear, myMonths.theMonth, COALESCE(theData.hours, 0)
FROM myMonths
LEFT JOIN (
    SELECT YEAR(date) AS theYear, MONTHNAME(date) AS theMonth, SUM(block_time) AS hours
    FROM flights
    WHERE operator = 'PSD'
    GROUP BY YEAR(date), MONTHNAME(date)
) theData
ON myMonths.theYear = theData.theYear
AND myMonths.theMonth = theData.theMonth