case when 统计

最新推荐文章于 2024-04-11 10:51:31 发布

a1008223

最新推荐文章于 2024-04-11 10:51:31 发布

阅读量106

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/a1008223/article/details/84088419

版权

Oracle 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

with a as(
select distinct
t.cus_type3,
sum(case when c.cat_no like 'A'||'%' then c.con_amt else 0 end) over (partition by t.cus_type3) ANUM3 ,
sum(case when c.cat_no like 'B'||'%' then c.con_amt else 0 end) over (partition by t.cus_type3) BNUM3,
sum(case when c.cat_no like 'C'||'%' then c.con_amt else 0 end) over (partition by t.cus_type3) CNUM3,
sum(case when t.cus_type3 ='01' then c.con_amt else 0 end ) over (partition by t.cus_type3 ) zy,
sum(case when t.cus_type3 ='02' then c.con_amt else 0 end ) over (partition by t.cus_type3 )sj,
sum(case when t.cus_type3 ='03' then c.con_amt else 0 end ) over (partition by t.cus_type3 )df,
sum(case when t.cus_type3 ='04' then c.con_amt else 0 end ) over (partition by t.cus_type3 )xq
from customer t,contract_dtl c
where t.cus_no= c.cus_no
)
select a.*,(select sum(ANUM3) from a) A3SUM,
(select sum(BNUM3) from a) B3SUM,
(select sum(CNUM3) from a) C3SUM,
(select sum(zy)+sum(sj)+sum(df)+sum(xq)from a) SUMAll3
from a