oracle 产生连续的日期,oracle 链接查询, 生成 连续日期

WITH T1 as(

select to_char( to_date('2011-04-01','yyyy-mm-dd') + rownum-1,'yyyy-mm-dd' ) as year

from dual

connect by to_date('2011-04-01','yyyy-mm-dd') + rownum-1 <=to_date('2011-04-05','yyyy-mm-dd')

),

T2 as(

select YEAR ,CHECKCOUNT FROM NORMAL

),

T3 as (

select YEAR ,CHECKCOUNT FROM FAIL

)

select T1.YEAR, T2.CHECKCOUNT from T1 LEFT JOIN T2 on( T1.YEAR=T2.YEAR );

select T1.YEAR, "NVL"(T2.CHECKCOUNT, 0) from T1 LEFT JOIN T2 on( T1.YEAR=T2.YEAR );

//第二部

WITH T1 as(

select to_char( to_date('2011-04-01','yyyy-mm-dd') + rownum-1,'yyyy-mm-dd' ) as year

from dual

connect by to_date('2011-04-01','yyyy-mm-dd') + rownum-1 <=to_date('2011-04-05','yyyy-mm-dd')

),

T2 as(

select YEAR ,CHECKCOUNT FROM NORMAL

),

T3 as (

select YEAR ,CHECKCOUNT FROM FAIL

)

select T1.YEAR, "NVL"(T2.CHECKCOUNT, 0), "NVL"(T3.CHECKCOUNT, 0) from T1 LEFT JOIN T2 on( T1.YEAR=T2.YEAR ) LEFT JOIN T3 on( T1.YEAR=T3.YEAR ) ORDER BY YEAR;

mybatis  在oracle 数据库生成 连续的日期

WITH datesel AS (

SELECT

TO_CHAR (

TO_DATE (#{beginQuerytime}, 'yyyy-mm-dd') + (ROWNUM - 1),

'yyyy-mm-dd'

) AS yearMonthDay

FROM

dual CONNECT BY ROWNUM <= (

TO_DATE (#{endQuerytime}, 'yyyy-mm-dd') - TO_DATE (#{beginQuerytime}, 'yyyy-mm-dd')

) + 1

),

tempName AS (

SELECT

TO_CHAR (CHECKTIME, 'yyyy-mm-dd') AS KEY,

COUNT (IDENTITYCODE)

VALUE

FROM

DIC_CHECKINFO

WHERE

CHECKTIME >= TO_DATE (#{beginQuerytime} || ' 00:00:00', 'yyyy-mm-dd HH24:mi:ss')

AND CHECKTIME <= TO_DATE (#{endQuerytime} || ' 23:59:59', 'yyyy-mm-dd HH24:mi:ss')

GROUP BY

TO_CHAR (CHECKTIME, 'yyyy-mm-dd')

),

tempCHECKFAIL AS (

SELECT

TO_CHAR (QUERYTIME, 'yyyy-mm-dd') AS KEY,

COUNT (IDENTITYCODE)

VALUE

FROM

DIC_CHECKFAIL

WHERE

QUERYTIME >= TO_DATE (#{beginQuerytime} || ' 00:00:00', 'yyyy-mm-dd HH24:mi:ss')

AND QUERYTIME <= TO_DATE (#{endQuerytime} || ' 23:59:59', 'yyyy-mm-dd HH24:mi:ss')

GROUP BY

TO_CHAR (QUERYTIME, 'yyyy-mm-dd')

) SELECT

D.yearMonthDay year_Month_Day,

NVL (T . VALUE, 0) check_Count,

NVL (c. VALUE, 0) fail_Count

FROM

datesel D

LEFT JOIN tempName T ON (D .yearMonthDay = T . KEY)

LEFT JOIN tempCHECKFAIL c ON (D .yearMonthDay = c. KEY)

ORDER BY

yearMonthDay DESC