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

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>mysql一条sql查出多个条件不同的sum或count问题

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>更新时间：2023年05月29日 09:50:02   作者：码农王山

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>这篇文章主要介绍了mysql一条sql查出多个条件不同的sum或count问题mysql一条sql查出多个条件不同的sum或count问题，具有很好的参考价值mysql sum if 多条件，希望对大家有所帮助。如有错误或未考虑完全的地方mysql一条sql查出多个条件不同的sum或count问题，望不吝赐教

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>一条sql查出多个条件不同的sum或count

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>最近做java后台遇到很多问题记录一下mysql sum if 多条件，有个需求是在一条sql中同时统计到几条条件不一样的sql或count，就用了子查询，希望能帮助到需要的朋友，上代码和效果图：

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”><pre class="brush:sql;" style="background: #e0ede8; color: #115e59; padding: 1.4em; border-radius: 4px; overflow-x: auto; margin: 1.8em 0; font-family: Consolas, Monaco, monospace; font-size: 14px; line-height: 1.6;”>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 

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>数据库表

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>1828.png” alt=”mysql sum if 多条件”/ style=”max-width: 100%; height: auto; display: block; margin: 1.8em auto; border-radius: 4px; box-shadow: 0 4px 20px rgba(17,94,89,0.12);”>

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>查询结果：

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>1929.png” alt=”mysql sum if 多条件”/ style=”max-width: 100%; height: auto; display: block; margin: 1.8em auto; border-radius: 4px; box-shadow: 0 4px 20px rgba(17,94,89,0.12);”>

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>11876415,351069070&fm=253&fmt=JPEG&app=138&f=JPEG?w=737&h=500″ alt=”mysql sum if 多条件”/ style=”max-width: 100%; height: auto; display: block; margin: 1.8em auto; border-radius: 4px; box-shadow: 0 4px 20px rgba(17,94,89,0.12);”>

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>Sql根据不同条件统计总数

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>经常会遇到根据不同的条件统计总数的问题，一般有两种写法：count和sum都可以

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>数据准备：

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”><img src="/wp-content/uploads/2026/10/1791176970293_3.png" alt="mysql sum if 多条件"/ style="max-width: 100%; height: auto; display: block; margin: 1.8em auto; border-radius: 4px; box-shadow: 0 4px 20px rgba(17,94,89,0.12);”>

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>方法一 ：Count

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>代码：

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”><pre class="brush:sql;" style="background: #e0ede8; color: #115e59; padding: 1.4em; border-radius: 4px; overflow-x: auto; margin: 1.8em 0; font-family: Consolas, Monaco, monospace; font-size: 14px; line-height: 1.6;”>SELECT
COUNT(
CASE
WHEN age > 20
AND age < 25 THEN
1
ELSE
NULL
END
) AS cnt0,
COUNT(
CASE
WHEN age >= 25
<span style="color: black; background-color: #FF1493; font-weight: bold;”>AND age < 30 THEN
1
ELSE
NULL
END
) AS cnt1
FROM
USER;

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>结果：

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>1.png” alt=”mysql sum if 多条件”/ style=”max-width: 100%; height: auto; display: block; margin: 1.8em auto; border-radius: 4px; box-shadow: 0 4px 20px rgba(17,94,89,0.12);”>

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>方法二：sum

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>代码：

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”><pre class="brush:sql;" style="background: #e0ede8; color: #115e59; padding: 1.4em; border-radius: 4px; overflow-x: auto; margin: 1.8em 0; font-family: Consolas, Monaco, monospace; font-size: 14px; line-height: 1.6;”>SELECT
SUM(
CASE
WHEN age > 20
AND age < 25 THEN
1
ELSE
0
END
) AS cnt0,
SUM(
CASE
WHEN age >= 25
<span style="color: black; background-color: #FF1493; font-weight: bold;”>AND age < 30 THEN
1
ELSE
0
END
) AS cnt1
FROM
USER;

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>结果：

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>100%; height: auto; display: block; margin: 1.8em auto; border-radius: 4px; box-shadow: 0 4px 20px rgba(17,94,89,0.12);”>

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>1.baidu.com/it/u=3773558467,2484497954&fm=253&fmt=JPEG&app=138&f=JPEG?w=580&h=500″ alt=”mysql sum if 多条件”/ style=”max-width: 100%; height: auto; display: block; margin: 1.8em auto; border-radius: 4px; box-shadow: 0 4px 20px rgba(17,94,89,0.12);”>

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>当然也可以和count代码一样ELSE后面也写为NULL

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”><pre class="brush:sql;" style="background: #e0ede8; color: #115e59; padding: 1.4em; border-radius: 4px; overflow-x: auto; margin: 1.8em 0; font-family: Consolas, Monaco, monospace; font-size: 14px; line-height: 1.6;”>SELECT
SUM(
CASE
WHEN age > 20
AND age < 25 THEN
1
ELSE
NULL
END
) AS cnt0,
SUM(
CASE
WHEN age >= 25
<span style="color: black; background-color: #FF1493; font-weight: bold;”>AND age < 30 THEN
1
ELSE
NULL
END
) AS cnt1
FROM
USER;

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>后记

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>其实原理很简单，count统计的时候有满足条件的就加1，没有满足的变为NULL，我们知道聚合函数统计的时候是忽略null值的；而sum原理和coun相似，不过ELSE后面可以是0或者NULL。

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>总结

<p style="margin: 1.2em 0; line-height: 2.0; font-size: 17px; color: #1f2937; text-align: justify;”>以上为个人经验mysql sum if 多条件，希望能给大家一个参考，也希望大家多多支持脚本之家。