首页 > 代码库 > 用料统计SQL
用料统计SQL
Itpub上遇到一个求助写SQL的帖子,感觉很有意思,于是写出来看看,要求如下:
有个计划表1, 记录物料的年度计划量
有个实际使用情况表2,记录实际使用情况。
最后要出个统计表,把计划和实际的数据结合到一起进行统计,
有特殊的情况就是可能部分物料有计划但是没有实际使用,有的物料无计划但是有实际使用情况,
要把这些数据合并到一起按照季度进行统计并算差异量,能实现吗??
于是建表插数据,表结构如下:
create table plan(OBJ varchar2(10),YEAR number(4),PLAN_COUNT number(10)); create table actual(OBJ varchar2(10),YEAR number(4),SEASON number(10),USED number(10));
语句如下:
with t1 as (
select distinct a.obj,p.plan_count,a.year,
case when a.season=1 then sum(a.used) over (partition by a.obj,a.season) else null end as s1,
case when a.season=2 then sum(a.used) over (partition by a.obj,a.season) else null end as s2,
case when a.season=3 then sum(a.used) over (partition by a.obj,a.season) else null end as s3,
case when a.season=4 then sum(a.used) over (partition by a.obj,a.season) else null end as s4,
sum(a.used) over (partition by a.obj) as sum_count
from actual a left join plan p on a.obj=p.obj order by 1,4
)
select temp.obj,nvl(temp.plan_count,0),temp.year,temp.q1,temp.q2,temp.q3,temp.q4,nvl(temp.sum_count,0),(nvl(temp.plan_count,0)-nvl(temp.sum_count,0)) as diff
from(
select distinct t1.obj,t1.plan_count,t1.year,
sum(s1) over (partition by t1.obj) as Q1,
sum(s2) over (partition by t1.obj) as Q2,
sum(s3) over (partition by t1.obj) as Q3,
sum(s4) over (partition by t1.obj) as Q4,t1.sum_count from t1
union all
select pl.obj,pl.plan_count,pl.year,null,null,null,null,null from plan pl where pl.obj not in (select distinct obj from actual)
) temp
order by 1
用到了nvl,case when,not in,over partition,sum,union,left join,with as等写法,常年写SQL的应该有更加效率的写法,有待发现之后补充到此进行完善。
用料统计SQL
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。