oracle中⽇期⾃动补全，没有数据⾃动补0
oracle中⽇期⾃动补全，没有数据⾃动补0
1. 描述
最近在项⽬中遇到⼀个问题，在进⾏oracle相关查询操作的时候，查询某个⽉的数据，这个⽉中某些⽇期没有数据，就造成了⽇期的缺失，我想做的就是不管有没有数据所有数据都要查询出来，没有数据的⾃动补0.
2. oracle相关查询代码
先当时做的数据库demo
⾃动补全⽇期代码
SELECT
TO_CHAR( ADD_MONTHS( TO_DATE( '2018-01', 'yyyy-MM' ), ROWNUM - 1 ), 'yyyy-MM' ) AS monthlist
FROM
DUAL CONNECT BY ROWNUM <= months_between(
to_date( '2018-12', 'yyyy-MM' ),
to_date( '2018-01', 'yyyy-MM' )) + 1
数据库查询代码，查询某个⽉的YJDJ总数
SELECT
to_char( DATETIME, 'yyyy-mm' ) AS DATETIME,
sum( YJDJ ) AS YJSUM
FROM
JC_T_YJXX
WHERE
YJDJ = 1
AND DATETIME BETWEEN to_date( '2018-01', 'yyyy-mm' )
AND to_date( '2018-12', 'yyyy-mm' )
GROUP BY
to_char( DATETIME, 'yyyy-mm' )
ORDER BY
to_char( DATETIME, 'yyyy-mm' )
进⾏左连接，对没有数据的⽉份补0
SELECT
nvl( b.YJSUM, 0 ) AS value
FROM
(
SELECT
TO_CHAR( ADD_MONTHS( TO_DATE( '2018-01', 'yyyy-MM' ), ROWNUM - 1 ), 'yyyy-MM' ) AS monthlist  FROM
DUAL CONNECT BY ROWNUM <= months_between(
to_date( '2018-12', 'yyyy-MM' ),
to_date( '2018-01', 'yyyy-MM' )) + 1
) a
LEFT JOIN (
SELECT
to_char( DATETIME, 'yyyy-mm' ) AS DATETIME,
sum( YJDJ ) AS YJSUM
FROM
JC_T_YJXX
WHERE
YJDJ = 1
AND DATETIME BETWEEN to_date( '2018-01', 'yyyy-mm' )
AND to_date( '2018-12', 'yyyy-mm' )
GROUP BY
to_char( DATETIME, 'yyyy-mm' )
ORDER BY
to_char( DATETIME, 'yyyy-mm' )) b hlist = b.DATETIME
ORDER BY
查询结果
上⾯是补全某⼀年⽉份，同理补全某⼀⽉所有⽇期代码如下：
oracle中trunc函数用法
SELECT
TO_CHAR( TO_DATE( '2018-08-01', 'yyyy-MM-dd' ) + ROWNUM - 1, 'yyyy-MM-dd' ) AS daylist
FROM
DUAL CONNECT BY ROWNUM <= trunc(
to_date( '2018-08-31', 'yyyy-MM-dd' ) - to_date( '2018-08-01', 'yyyy-MM-dd' )) + 1
关于trunc函数的⽤法
1.select trunc(sysdate) from dual --2013-01-06 今天的⽇期为2013-01-06
2.select trunc(sysdate, 'mm') from dual --2013-01-01 返回当⽉第⼀天.
3.select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第⼀天
4.select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年⽉⽇
5.select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第⼀天
6.select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第⼀天
7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35
8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确