mysql一条sql查出多个条件不同的sum或count

最近做java后台遇到很多问题记录一下,有个需求是在一条sql中同时统计到几条条件不一样的sql或count,就用了子查询,希望能帮助到需要的朋友,上代码和效果图:

select 
sum(pzgwjscd) as allJsgm,sbjh.nd as nd, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3301') and ptgw.zt != 0 and sbjh.nd=2017 ) as hzJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3303') and ptgw.zt != 0 and sbjh.nd=2017) as wzJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3304') and ptgw.zt != 0 and sbjh.nd=2017 ) as jxJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3305') and ptgw.zt != 0 and sbjh.nd=2017) as huzJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3306') and ptgw.zt != 0 and sbjh.nd=2017 ) as sxJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3307') and ptgw.zt != 0 and sbjh.nd=2017 ) as jhJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3308') and ptgw.zt != 0 and sbjh.nd=2017 ) as qzJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3309') and ptgw.zt != 0 and sbjh.nd=2017 ) as zsJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3310') and ptgw.zt != 0 and sbjh.nd=2017 ) as tzJsgm, 
( select sum(pzgwjscd) from fm_jh_ws_ptgw ptgw left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where substring(ptgw.xzqh,1,4) LIKE CONCAT ('3311') and ptgw.zt != 0 and sbjh.nd=2017 ) as lsJsgm 
from fm_jh_ws_ptgw ptgw 
left join fm_sb_jh sbjh on ptgw.sbid = sbjh.id 
where 1=1 and ptgw.zt != 0 and sbjh.nd=2017 

数据库表


查询结果:



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值