快捷導(dǎo)航

mysql一條sql查出多個條件不同的sum或count問題

更新時(shí)間：2023年05月29日 09:50:02 作者：碼農(nóng)王山

這篇文章主要介紹了mysql一條sql查出多個條件不同的sum或count問題，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

一條sql查出多個條件不同的sum或count

最近做java后臺遇到很多問題記錄一下，有個需求是在一條sql中同時(shí)統(tǒng)計(jì)到幾條條件不一樣的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?

數(shù)據(jù)庫表

查詢結(jié)果：

Sql根據(jù)不同條件統(tǒng)計(jì)總數(shù)

經(jīng)常會遇到根據(jù)不同的條件統(tǒng)計(jì)總數(shù)的問題，一般有兩種寫法：count和sum都可以

數(shù)據(jù)準(zhǔn)備：

方法一：Count

代碼：

SELECT
	COUNT(
		CASE
		WHEN age > 20
		AND age < 25 THEN
			1
		ELSE
			NULL
		END
	) AS cnt0,
	COUNT(
		CASE
		WHEN age >= 25
		AND age < 30 THEN
			1
		ELSE
			NULL
		END
	) AS cnt1
FROM
	USER;

結(jié)果：

方法二：sum

代碼：

SELECT
	SUM(
		CASE
		WHEN age > 20
		AND age < 25 THEN
			1
		ELSE
			0
		END
	) AS cnt0,
	SUM(
		CASE
		WHEN age >= 25
		AND age < 30 THEN
			1
		ELSE
			0
		END
	) AS cnt1
FROM
	USER;

結(jié)果：

當(dāng)然也可以和count代碼一樣ELSE后面也寫為NULL

SELECT
	SUM(
		CASE
		WHEN age > 20
		AND age < 25 THEN
			1
		ELSE
			NULL
		END
	) AS cnt0,
	SUM(
		CASE
		WHEN age >= 25
		AND age < 30 THEN
			1
		ELSE
			NULL
		END
	) AS cnt1
FROM
	USER;