oracle如何缺上月的日期,ORACLE SQL：填写缺少的日期

您可以从SYSDATE获取30天的时间段(我假设您要包含SYSDATE？)：

WITH mydates AS (

SELECT TRUNC(SYSDATE) - 1 + LEVEL AS due_date FROM dual

CONNECT BY LEVEL <= 31

)

然后使用上面的代码对您的查询进行LEFT JOIN(将查询放入CTE也许不是一个坏主意)：

WITH mydates AS (

SELECT TRUNC(SYSDATE) - 1 + LEVEL AS due_date FROM dual

CONNECT BY LEVEL <= 31

), myorders AS (

select

(case when trunc(so.revised_due_date) <= trunc(sysdate)

then trunc(sysdate) else trunc(so.revised_due_date) end) due_date,

(case

when (case when sp.pr_typ in ('VV','VD') then 'DVD' when sp.pr_typ in ('RD','CD')

then 'CD' end) = 'CD'

and (case when so.tec_criteria in ('PI','MC')

then 'XX' else so.tec_criteria end) = 'OF'

then sum(so.revised_qty_due)

end) CD_OF_VOLUME

from shop_order so

left join scm_prodtyp sp

on so.prodtyp = sp.prodtyp

where so.order_type = 'MD'

and so.plant = 'W'

and so.status_code between '4' and '8'

and trunc(so.revised_due_date) <= trunc(sysdate)+30

group by trunc(so.revised_due_date), so.tec_criteria, sp.pr_typ

order by trunc(so.revised_due_date)

)

SELECT mydates.due_date, myorders.cd_of_volume

FROM mydates LEFT JOIN myorders

ON mydates.due_date = myorders.due_date;

如果要在“缺失”日期而不是NULL上显示零,请使用上面的COALESCE(myorders.cd_of_volume,0)AS cd_of_volume.