SELECT TO_CHAR(TO_DATE('2019-08-01', 'YYYY-MM-DD') + ROWNUM - 1, 'YYYY-MM-DD') DAY_TIME
FROM DUAL
CONNECT BY ROWNUM <= TO_DATE('2019-09-01', 'YYYY-MM-DD') - TO_DATE('2019-08-01', 'YYYY-MM-DD')
查询结果：
2019-08-01
2019-08-02
2019-08-03
2019-08-04
2019-08-05
2019-08-06
2019-08-07
2019-08-08
2019-08-09
2019-08-10
2019-08-11
2019-08-12
2019-08-13
2019-08-14
2019-08-15
2019-08-16
2019-08-17
2019-08-18
2019-08-19
2019-08-20
2019-08-21
2019-08-22
2019-08-23
2019-08-24
2019-08-25
2019-08-26
2019-08-27
2019-08-28
2019-08-29
2019-08-30
2019-08-31
这种查询数据，用于一种应用场景，统计某个月的某个产品的销售数量，单表查询。如果某一天没有销售，那么数据库当日是没有数据的，但是我们要的效果却是想让当天也查询出来，数量为0。这种情况，就把真正的数据查询出来，跟上面的日期查询进行right join右关联查询，nvl(pnum，0)默认没有值为0，即可。
SELECT nvl(a.pnum,0) as pnum,b.DAY_TIME as createTime from (
SELECT createTime,sum(pnum) pnum FROM (
SELECT to_char(t.CREATE_TIME,'yyyy-mm-dd') createTime,t.type_code typeCode,COUNT(1) pnum
FROM prot_sale
WHERE t.CREATE_TIME >= TO_DATE(#{btime}, 'YYYY-MM-DD')
AND t.CREATE_TIME <= TO_DATE(#{etime}, 'YYYY-MM-DD')
AND t.type_code IN
#{item}
GROUP BY to_char(t.CREATE_TIME,'yyyy-mm-dd'),t.type_code
ORDER BY to_char(t.CREATE_TIME,'yyyy-mm-dd') asc
) GROUP BY createTime
) a
right join
(SELECT TO_CHAR(TO_DATE(#{btime}, 'YYYY-MM-DD') + ROWNUM - 1, 'YYYY-MM-DD') DAY_TIME
FROM DUAL
CONNECT BY ROWNUM <= TO_DATE(#{etime}, 'YYYY-MM-DD') - TO_DATE(#{btime}, 'YYYY-MM-DD')
) b
ON a.createTime=b.DAY_TIME
ORDER BY b.DAY_TIME
3185
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
