在实习中遇到了一个问题前端传入两个日期要求将两个日期之间所有的数据都给查询出来,可是数据库中有很多日期都没有数据。如下,测试库中只有22年9月到23年2月的数据,
可以用以下sql,能查出22年3月到23年3月的所有日期,将这个表作为主表,将要查的数据left join进去
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2022-03','YYYY-MM'), ROWNUM - 1), 'YYYY-MM') AS dateTime FROM DUAL
CONNECT BY ROWNUM <= CEIL(MONTHS_BETWEEN(TO_DATE('2023-03', 'YYYY-MM'), TO_DATE('2022-03','YYYY-MM'))+1)
SELECT
t1.dateTime dateTime,
t2.NEW_ASSETS,0 newAssets,
t2.NEW_VERSION_NUM,0 newVersionNum,
t2.NEW_MODEL_NUM,0 newModelNum from
(SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2022-03','YYYY-MM'), ROWNUM - 1), 'YYYY-MM') AS dateTime FROM DUAL
CONNECT BY ROWNUM <= CEIL(MONTHS_BETWEEN(TO_DATE('2023-03', 'YYYY-MM'), TO_DATE('2022-03','YYYY-MM'))+1))t1
LEFT JOIN (SELECT DATE_TIME ,NEW_ASSETS,NEW_VERSION_NUM,NEW_MODEL_NUM FROM SJZD_MODEL_MONITOR ) t2 ON t2.DATE_TIME = t1.dateTime
ORDER BY dateTime ASC
可是这样没有数据的日期查出来为null 前端要求没有数据传0
可以用nvl函数来填充0
SELECT
t1.dateTime dateTime,
NVL(t2.NEW_ASSETS,0) newAssets,
NVL(t2.NEW_VERSION_NUM,0) newVersionNum,
NVL(t2.NEW_MODEL_NUM,0) newModelNum from
(SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2022-03','YYYY-MM'), ROWNUM - 1), 'YYYY-MM') AS dateTime FROM DUAL
CONNECT BY ROWNUM <= CEIL(MONTHS_BETWEEN(TO_DATE('2023-03', 'YYYY-MM'), TO_DATE('2022-03','YYYY-MM'))+1))t1
LEFT JOIN (SELECT DATE_TIME ,NEW_ASSETS,NEW_VERSION_NUM,NEW_MODEL_NUM FROM SJZD_MODEL_MONITOR ) t2 ON t2.DATE_TIME = t1.dateTime
ORDER BY dateTime ASC
成了!