SQL语句如下:
with tb as (
select COUNT(*) ZS ,T.JYZT,S.BUSINNAME
FROM TRANS_CRZYJBXXTBL T
JOIN SYS_DICTIONARY S
ON T.SSXZQ=S.BUSINID WHERE T.RESOURCELB='01' GROUP BY T.JYZT,S.BUSINNAME
)
select BUSINNAME,
sum(case when jyzt = '42' then ZS else 0 end) as WCJ,
sum(case when jyzt = '41' then ZS else 0 end) as CJ,
sum(case when jyzt = '11' then ZS else 0 end) as GPWKS,
sum(case when jyzt = '12' then ZS else 0 end) as GPQ,
sum(case when jyzt = '30' then ZS else 0 end) as ZZ,
sum(case when jyzt = '44' then ZS else 0 end) as ZhongZ
from tb group by BUSINNAME
上面语句要怎样写,求教高玩解答。并且假如我要对上面查询出的结果:
BUSINNAME WCJ CJ GPWKS GPQ ZZ ZhongZ
湖南 0 2 0 4 1 0
湖北 1 1 2 0 0 3
进行总数统计,要得到如下效果,该怎么写?
TOTAL TWCJ TCJ TGPWKS TGPQ TZZ TZhongZ
11 1 3 2 4 1 3
本人初上班不久,很多不懂地方,求指导,明天就要交了,急求,在线坐等
with tb as (
select COUNT(*) ZS ,T.JYZT,S.BUSINNAME
FROM TRANS_CRZYJBXXTBL T
JOIN SYS_DICTIONARY S
ON T.SSXZQ=S.BUSINID WHERE T.RESOURCELB='01' GROUP BY T.JYZT,S.BUSINNAME
)
select BUSINNAME,
sum(case when jyzt = '42' then ZS else 0 end) as WCJ,
sum(case when jyzt = '41' then ZS else 0 end) as CJ,
sum(case when jyzt = '11' then ZS else 0 end) as GPWKS,
sum(case when jyzt = '12' then ZS else 0 end) as GPQ,
sum(case when jyzt = '30' then ZS else 0 end) as ZZ,
sum(case when jyzt = '44' then ZS else 0 end) as ZhongZ
from tb group by BUSINNAME
上面语句要怎样写,求教高玩解答。并且假如我要对上面查询出的结果:
BUSINNAME WCJ CJ GPWKS GPQ ZZ ZhongZ
湖南 0 2 0 4 1 0
湖北 1 1 2 0 0 3
进行总数统计,要得到如下效果,该怎么写?
TOTAL TWCJ TCJ TGPWKS TGPQ TZZ TZhongZ
11 1 3 2 4 1 3
本人初上班不久,很多不懂地方,求指导,明天就要交了,急求,在线坐等
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货