sql分组查询groupby结合count，sum统计语句的实现（附带sql详细分析步骤）
⽇常写代码经常会遇到数据统计的业务场景，分组查询 group by 结合 count 和 sum 的复杂语句写起来容易令⼈头⼤，在这⾥分享两个⽐较复杂的统计场景，提供详细分析思路和最终sql语句，希望能给⼤家带来帮助。
场景1：统计每个学⽣的加减分次数和总分
库表结构如下所⽰：
学⽣分数表：student_score
字段名含义字段类型备注
id⾃增编号bigint
student_name学⽣姓名varchar
score分数double
sort分数类型int1-加分；2-减分；
is_delete删除标志位int默认为0；
⼀上来不着急写 sql，先来分析⼀下：
因为统计的是每个学⽣的分数，所以根据学⽣名称 student_name 进⾏ group by 分组查询。
需要获取的字段包括 学⽣名称 student_name，加分次数 add_count，扣分次数 sub_count，总加分 add_score，总扣分 sub_score，其中，学⽣名称不需要计算，因此只需处理次数和分数。
我们知道，count() 主要⽤于求⾏的个数累计，所以当分数类型 sort 为 1，则增加加分次数，sort 为 2，则增加扣分次数；⽽ sum() ⽤于求和累加，因⽽使⽤ sum() 来计算总分，分数类型 sort 为 1，则加分，sort 为 2，则减分。
有了清晰的思路，sql就不难写了：
SELECT
student_name,
count(sort =1OR NULL) add_count,
count(sort =2OR NULL) sub_count,
sum(IF(sort =1, score,0)) add_score,
sum(IF(sort =2, score,0)) sub_score
FROM
student_score
WHERE
is_delete =0
GROUP BY
student_name
原始表数据：
统计结果：
这⾥的次数和分数的条件判断是通过 if 语句来实现的，我们也可以通过 case when 语句来实现：
SELECT
student_name,
count(case when sort =1then1else null end) add_count,
count(case when sort =2then1else null end) sub_count,
sum(case when sort =1then score else0end) add_score,
sum(case when sort =2then score else0end) sub_score
FROM
student_score
WHERE
is_delete =0
groupby分组GROUP BY
student_name
场景2：按照权重统计每个学⽣的体测成绩
体育测试中我们要根据不同的指标对学⽣进⾏打分，⽬前分为5项指标：800⽶，50⽶，⽴定跳远，仰卧起坐和坐位体前屈。总分为100分，每项的分数权重占⽐不⼀样，如下表所⽰：
项⽬分数占⽐（%）
800⽶30
50⽶15
⽴定跳远20
仰卧起坐15
坐位体前屈20
其中⽴定跳远需要测试3次，每次都进⾏打分，需要根据这3次的得分计算出平均分作为最终得分，未来其他指标也可能采⽤这种⽅式进⾏打分。
需求明确了，先来设计数据库表，由于每项指标的权重是固定的，可以采⽤单独⼀个表来存储指标权重：
指标权重表：index_score
字段名含义字段类型备注
id⾃增编号bigint
index_code指标编号varchar
index_name指标名称varchar
share指标权重int
字段名含义字段类型备注
is_delete删除标志位int默认为0；
由于库表中需要保存全量详细的体测记录，所以需要记录每个学⽣的指标和对应的评分。这⾥通过指标编号进⾏关联，结果记录表如下所⽰：
结果记录表：score_record
字段名含义字段类型备注
id⾃增编号bigint
student_name学⽣名称varchar
index_code指标编号varchar
score对应指标得分double
is_delete删除标志位int默认为0；
我们需要通过⼀个sql关联 指标权重表 和 结果记录表 来获取每个学⽣的姓名和总得分。这个场景⽐较复杂，⼀步⼀步来分析：
⾸先先来处理最复杂的⽴定跳远项，先计算每个学⽣的3次得分的平均分，由于未来其他指标也可能采⽤这种⽅式进⾏打分，那么我们就要根据学⽣姓名和指标编号共同进⾏ group by 分组后再获取平均值：
SELECT
student_name,
index_code,
round(avg(score),1) index_avg
FROM
score_record r
WHERE
r.is_delete =0
GROUP BY
student_name, index_code
统计结果：
统计出每个学⽣的各项指标得分就完成了第⼀步，第⼆步需要根据各项指标的权重结合得分计算总分。这就需要结合指标权重表来获取每项指标的权重占⽐ share，再乘以第⼀步计算出的平均值 index_avg 后，最后使⽤ sum() 来计算出总分。
当然这⾥需要根据学⽣姓名来进⾏分组，对于计算出的总分还要使⽤ round() 函数保留两位⼩数，最终 sql 如下：
SELECT
round(sum(s.share* t.index_avg)/100,2) score, t.student_name
FROM
(
SELECT
student_name,
index_code,
round(avg(score),1) index_avg
FROM
score_record r
WHERE
r.is_delete =0
GROUP BY
student_name,index_code
) t
LEFT JOIN index_score s ON t.index_code = s.index_code
GROUP BY
t.student_name
这样⼀个看起来有点复杂的 sql，采⽤从⾥到外逐步分析的⽅式，也就可以轻松写出来了。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。