oracle获取数天前每日统计

获得数天前每天的日期

SELECT FDATE   FROM (SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 AS FDATE           FROM DUAL         CONNECT BY LEVEL <= 5) T  WHERE TO_CHAR(FDATE, 'MM') = TO_CHAR(SYSDATE, 'MM')

执行结果

组合代码获得数天前的统计总数

SELECT   days.createTime createTime ,  nvl (m.mdcount, 0) countsFROM  (SELECT     to_char(SYSDATE- LEVEL + 1, 'yyyy-mm-dd') createTime    FROM    DUAL connect BY LEVEL <= 15) days   LEFT JOIN     (SELECT       to_char(B.CREATETIME, 'yyyy-mm-dd') createTime,      nvl (COUNT(B.YDHQSH), 0) mdcount    FROM      tableName B where tb_type = '001'    GROUP BY to_char(B.CREATETIME, 'yyyy-mm-dd')) m     ON days.createTime  = m.createTime GROUP BY days.createTime ,m.mdcount  ORDER BY days.createTime   

执行结果