想请教大们一个问题关于sql的问题

我有一批数据，他会不定时的上传，可能最后生成的数据是
2020-01-01 1
2020-01-02 2
2020-01-04 4
2020-01-06 6
他三号跟五号没有数据，但是最后统计的时候，想显示出来的结果带着那两天，数据为 0就可以
如下：
2020-01-01 1
2020-01-02 2
2020-01-03 0
2020-01-04 4
2020-01-05 0
2020-01-06 6
请教各位


SET @start_date := '2020-01-01', @end_date := '2020-01-06';

SELECT d.date, COALESCE(data.value, 0) AS value
FROM (
  SELECT DATE_ADD(@start_date, INTERVAL t4+t3*10+t2*100+t1*1000 DAY) AS date
  FROM (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS T1
  CROSS JOIN (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS T2
  CROSS JOIN (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS T3
  CROSS JOIN (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2) AS T4
  WHERE DATE_ADD(@start_date, INTERVAL t4+t3*10+t2*100+t1*1000 DAY) BETWEEN @start_date AND @end_date
) d
LEFT JOIN data ON d.date = data.date
ORDER BY d.date ASC;

1、可以使用 CONNECT BY 方式：

WITH t AS (
        SELECT to_date( '2020-01-01', 'yyyy-mm-dd' ) d, 1 n FROM dual UNION ALL
        SELECT to_date( '2020-01-02', 'yyyy-mm-dd' ) d, 2 n FROM dual UNION ALL
        SELECT to_date( '2020-01-04', 'yyyy-mm-dd' ) d, 4 n FROM dual UNION ALL
        SELECT to_date( '2020-01-06', 'yyyy-mm-dd' ) d, 6 n FROM dual )
    , d AS (
        SELECT min_d + ROWNUM - 1 AS d
          FROM ( SELECT MAX( d ) + 1 max_d, MIN( d ) min_d FROM t ) t1
         CONNECT BY min_d + ROWNUM <= max_d )
SELECT d.d, NVL( SUM( t.n ), 0 ) n
  FROM d LEFT JOIN t ON d.d = t.d
 GROUP BY d.d
 ORDER BY d.d;

输出结果：

img

可以参考下这个 orcal查询12个月份数据，没有则用0补充 http://t.csdn.cn/Uvajy