mysql查询连续日月年统计sql数据 无数据补0

日统计

SELECT DATE_FORMAT(dt.d,'%Y-%m-%d'), (SELECT COUNT(*) FROM cases_deal_record c WHERE YEAR(dt.d)=YEAR(c.create_time) AND MONTH(dt.d)=MONTH(c.create_time) AND DAY(dt.d)=DAY(c.create_time)) c
FROM
(SELECT DATE_SUB('2021-01-01',INTERVAL @i:=@i+1 DAY) AS d
FROM (SELECT 1 FROM cases_deal_record LIMIT 5) AS tmp,(SELECT @i:= -1) t) dt

 

月统计

SELECT DATE_FORMAT(dt.d,'%Y-%m'), (SELECT COUNT(*) FROM cases_deal_record c WHERE YEAR(dt.d)=YEAR(c.create_time) AND MONTH(dt.d)=MONTH(c.create_time)) c
FROM
(SELECT DATE_SUB(CURDATE(),INTERVAL @i:=@i+1 MONTH) AS d
FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS tmp,(SELECT @i:= -1) t) dt

年统计

SELECT DATE_FORMAT(dt.d,'%Y'), (SELECT COUNT(*) FROM cases_deal_record c WHERE YEAR(dt.d)=YEAR(c.create_time)) c
FROM
(SELECT DATE_SUB(CURDATE(),INTERVAL @i:=@i+1 YEAR) AS d
FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS tmp,(SELECT @i:= -1) t) dt

1.构造日期虚拟表

两种方式:

SELECT 1 UNION ALL SELECT 1方式需要程序动态生成

SELECT 1 FROM cases_deal_record LIMIT 5  (limit代表时间间隔) 可查任意表  但注意记录数要大于时间间隔数

2.根据虚拟表查询统计