前端时间做项目的时候,在做一个报表,发现入库的时候,有几天是没数据的,所以做报表查询的时候,没有数据的日期就被跳过了,导致了报表的日期不是连续的。
产品希望日期是连续的,数据没有就自动补0,这样更利于呈现报表。
那么可以参考以下的SQL:
SELECT
IFNULL( d2.active, 0 ) AS active,
IFNULL( d2.install, 0 ) AS install,
t1.time
FROM
(
SELECT
SUM( active ) AS active,
SUM( INSTALL ) AS install,
time AS date
FROM
tp_record
GROUP BY
time
ORDER BY
time DESC
) d2
RIGHT JOIN (
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) time
FROM
( SELECT @cdate := date_add( '2019-12-11', INTERVAL + 1 DAY ) FROM tp_record GROUP BY time ORDER BY time DESC ) d1
WHERE
@cdate > date_add( '2019-12-11', INTERVAL datediff( "2019-11-11", "2019-12-11" ) DAY )
) t1 ON d2.date = t1.time