场景:
在坐数据展示时,通过时间group by只显示数据库有记录的时间,没有记录不显示、前段坐图表还需这些数据,缺失的日期自动补0
解决办法
1、查出数据后代码补全数据
2、添加一张日历表、初始化几年的数据,关联进行查询
3、纯sql实现:
####测试数据
SELECT
DATE( u.createdDate ) AS create_time,
count( u.id ) AS total
FROM
hr_order u
where
u.createdDate> '2021-10-01'
GROUP BY
DATE( u.createdDate );
;运行结果:
###test sql
SET @i :=- 1;
SELECT
x.`time`,
IFNULL( d.total, 0 ) AS total
FROM
(
SELECT
date_format( DATE_SUB( NOW( ), INTERVAL ( @i := @i + 1 ) DAY ), '%Y-%m-%d' ) AS `time`
FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2
WHERE
@i <= 30
) x
LEFT JOIN
(
SELECT
DATE( u.createdDate ) AS createdDate,
count( u.id ) AS total
FROM
hr_order u
where
u.createdDate> '2021-10-01'
GROUP BY
DATE( u.createdDate )
) d ON TO_DAYS( x.`time` ) = TO_DAYS( DATE( d.createdDate ) )
ORDER BY
x.`time`
运行结果: