MySQL中连续某月日期内数据统计,缺省天数0补全

总共有两种方式

第一种数据库表中数据的总数没有限制

SELECT t1.updateTime, COALESCE(t2.count, 0) as count
FROM( SELECT ADDDATE(y.first, x.d - 1) as updateTime FROM
(
SELECT 1 AS d 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 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 31
) x,
(
SELECT ‘2019-12-01’ - INTERVAL DAY(‘2019-12-01’) - 1 DAY AS first,
DAY(LAST_DAY(‘2019-12-01’)) AS last) y
WHERE x.d <= y.last
) t1
LEFT JOIN(

	SELECT count(1) count ,pr.distinct_date 
             from patrol_report pr left join village_community vc on pr.community_id = vc.id  where 1=1 and pr.community_id = '123' and pr.update_time like '%2019-12%'  GROUP BY pr.distinct_date  

) t2
on t1.updateTime = t2.distinct_date ORDER BY updateTime

第二种数据库表中数据应该首先插入31条数据（保证不少于31条），否则只会显示当前日期前面的数据。

SELECT t1.date_str, COALESCE(t2.count, 0) as date_total_count
FROM(
SELECT
@num:=@num+1 num,
DATE_ADD(DATE_FORMAT(‘2019-12-01’, ‘%Y-%m-%d’),INTERVAL @num DAY) as date_str
FROM
patrol_report a,
(select @num:=-1) num_t
WHERE
@num < (SELECT DAYOFMONTH(LAST_DAY(‘2019-12-01’)) - 1)
) t1
LEFT JOIN(

	SELECT count(1) count ,pr.distinct_date 
             from patrol_report pr left join village_community vc on pr.community_id = vc.id  where 1=1 GROUP BY pr.distinct_date  

) t2
on t1.date_str = t2.distinct_date ORDER BY date_str

标题