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
<foreach item="item" index="index" collection="typeList" open="(" separator="," close=")">
#{item}
</foreach>
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