mysql填充两个日期范围内缺少的月份

Question

我需要填写此查询中缺少的月份，并将缺少的月份设置为0个元素：(

SELECT MONTH(timestamp), YEAR(timestamp), count(*) as Total
FROM dialogs
WHERE timestamp BETWEEN '2021-09-01' AND '2023-01-01'
GROUP BY YEAR(timestamp), MONTH(timestamp);

结果是：

7,2022,354
8,2022,4715
9,2022,2712
10,2022,1740

但我期待类似的东西：

6,2022,0 // <--- missing month in table
7,2022,354
8,2022,4715
9,2022,2712
10,2022,1740
11,2022,0 // <--- missing month in table

等等。

Answer 1

您将需要一个完整的日历

WITH recursive years AS (
select 2010 as yr
union all
select yr + 1 
from years
where yr <= 2030)
, months AS (
select 1 as mon
union all
select mon + 1 
from months
where mon <= 12) 
SELECT cal.yr, cal.mon, ifnull(tbl.total,0) total
FROM (SELECT MONTH(timestamp) mon, YEAR(timestamp) yr, count(*) as Total
FROM dialogs
GROUP BY YEAR(timestamp), MONTH(timestamp)) tbl
right join ( select * from years join months ) cal ON cal.mon=tbl.mon and    cal.yr=tbl.yr
WHERE cal.yr BETWEEN 2019 AND 2023 
order by 1,2;