mysql - SQL技巧按月计算行数？

Question

我有一个基本的 SQL 查询:

SELECT MONTH(created_at), COUNT(id) as total FROM `clients` GROUP BY MONTH(created_at)

它返回按月分组的数据，如下所示:

MONTH(created_at) | total
09                   1
10                   2

如何将剩余月份补零？所以，结果我需要得到所有月份:

MONTH(created_at) | total
09                   1
..                   2
12                   5

我这样试过:

SELECT months.id, COUNT(clients.id) as total FROM `months` LEFT JOIN `clients` ON MONTH(created_at) = months.id GROUP BY MONTH(created_at)

Answer 1

使用包含所有月份数字的派生表，然后左连接您的表。

SELECT mths.mth, COUNT(c.id) as total
FROM (select 1 as mth union select 2 union select 3 union
      select 4 union select 5 union select 6 union select 7 union
      select 8 union select 9 union select 10 union select 11 union select 12) mths
LEFT JOIN `clients` c on mths.mth=month(c.created_at)
GROUP BY mths.mth