# sql分组查询group by结合count，sum统计语句的实现（附带sql详细分析步骤）

15 篇文章 2 订阅

## 场景1：统计每个学生的加减分次数和总分

id自增编号bigint
student_name学生姓名varchar
score分数double
sort分数类型int1-加分；2-减分；
is_delete删除标志位int默认为0；

SELECT
student_name,
count(sort = 1 OR NULL) add_count,
count(sort = 2 OR 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


SELECT
student_name,
count(case when sort = 1 then 1 else null end) add_count,
count(case when sort = 2 then 1 else null end) sub_count,
sum(case when sort = 1 then score else 0 end) add_score,
sum(case when sort = 2 then score else 0 end) sub_score
FROM
student_score
WHERE
is_delete = 0
GROUP BY
student_name


## 场景2：按照权重统计每个学生的体测成绩

800米30
50米15

id自增编号bigint
index_code指标编号varchar
index_name指标名称varchar
share指标权重int
is_delete删除标志位int默认为0；

id自增编号bigint
student_name学生名称varchar
index_code指标编号varchar
score对应指标得分double
is_delete删除标志位int默认为0；

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


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


• 3
点赞
• 2
评论
• 3
收藏
• 一键三连
• 扫一扫，分享海报

08-21 9421
01-17 861

12-22 7434
05-06 14万+
07-26 2万+
06-20 3万+
12-31 1309
11-10 2万+
03-26 1万+
01-03 5万+
05-23 6293
05-29 2万+
10-10 1万+