查询近一年的每月count，如果没有则显示为0

查询近一年的每月count，如果没有则显示为0
SELECT A.id,IF(C.COUNT3 IS NULL ,0,C.COUNT3) from
(
select 0 as id FROM DUAL
UNION ALL
select 1 as id FROM DUAL
UNION ALL
select 2 as id FROM DUAL
UNION ALL
select 3 as id FROM DUAL
UNION ALL
select 4 as id FROM DUAL
UNION ALL
select 5 as id FROM DUAL
UNION ALL
select 6 as id FROM DUAL
UNION ALL
select 7 as id FROM DUAL
UNION ALL
select 8 as id FROM DUAL
UNION ALL
select 9 as id FROM DUAL
UNION ALL
select 10 as id FROM DUAL
UNION ALL
select 11 as id FROM DUAL
) as A
LEFT JOIN
(select TIMESTAMPDIFF
(month,sysdate(),B.tag_time)
as MONTH2,
count(0) AS COUNT3
from cipol_task_result B ) C on A.id = C.MONTH2

返回两个值，第一个值是距离现在有几个月，第二个是count值
这个DUAL处理不好，正常可以行转列去left join，也有人说应该在java去处理

SELECT
DATE_FORMAT( date_add(concat(‘2022-01-01’), interval(help_topic_id) DAY),‘%Y-%m-%d’) date
FROM
mysql.help_topic
WHERE
help_topic_id <= timestampdiff(DAY,concat(‘2022-01-01’),concat(‘2022-12-31’))

这个方法会返回两个日期区间的所有天，不知道为什么图片上传违规，自己测试一下，我用的8.0是可以的，不知道5.6有没有问题。

如果要取月份的话则外边套一个查询，把日截掉，然后按月份分组
select left(date,7) data from (
select date from (
SELECT
DATE_FORMAT( date_add(concat(‘2022-01-01’), interval(help_topic_id) DAY),‘%Y-%m-%d’) date
FROM
mysql.help_topic
WHERE
help_topic_id <= timestampdiff(DAY,concat(‘2022-01-01’),concat(‘2022-12-31’))
)a )b GROUP BY data
这个就返回两个日期之间所有的月份啦。

之后就可以进行连接查询你想要的表了

5.6版本为
set @i = -1;
set @sql = repeat(" select 1 union all",-datediff(‘2021-11-01’,‘2021-12-31’)+1);
set @sql = left(@sql,length(@sql)-length(" union all"));
set @sql = concat(“select date_add(‘2021-11-01’,interval @i:=@i+1 day) as date from (”,@sql,“) as tmp”);
prepare stmt from @sql;
execute stmt
这个不是返回一个视图

select a.date
from (
select ‘2022-02-20’ + INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.date between ‘2022-02-20’ and ‘2022-3-5’ ORDER BY a.date asc
这个通过乘法一直算出每年的天数，然后给定区间就可以返回日期了