ORACLE SQL:填写缺少的日期
发布时间:2020-12-12 13:10:19 所属栏目:百科 来源:网络整理
导读:我有以下代码,它给出了30天的生产日期和生产量. 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.
我有以下代码,它给出了30天的生产日期和生产量.
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) 我遇到的问题是没有计划生产的日期,日期不会出现在报告上.有没有办法填写缺少的日期. 即当前报告显示以下内容…… DUE_DATE CD_OF_VOLUME 14/04/2015 35,267.00 15/04/2015 71,744.00 16/04/2015 20,268.00 17/04/2015 35,156.00 18/04/2015 74,395.00 19/04/2015 3,636.00 21/04/2015 5,522.00 22/04/2015 15,502.00 04/05/2015 10,082.00 注意:缺少日期(2015年4月20日,2015年4月23日至2015年5月3日) 范围始终是sysdate的30天. 谢谢 您可以从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','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),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. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |