SQL练习题之求平均分低于80分的班级学生各科成绩并合计学生总分

是对是错也好 不必说了
是怨是爱也好 不须揭晓
何事更重要 比两心的需要
柔情蜜意怎么可缺少
——《说不出再见》谭咏麟

前言

昨天晚上在回答一个小伙伴的SQL问题时，发现这个问题很有代表性，考察到多个SQL语法和逻辑思维，在此分享给大家，可以作为SQL练习题使用，欢迎一起讨论多种解题思路。

一、练习题题目

现有一个班级学生各学科成绩表，求平均分低于80分的班级学生各科成绩并合计学生总分（要求合计作为行位于该同学各学科之下）

最终结果类似下面截图，可以看作每个班每个学生为一组，先展示其各学科成绩，再生成合计行。

二、创建测试数据

（一）创建测试表

创建表 test_20230724，包含如下字段： class（班级），name（学生姓名），course（学科），score（分数）。

create table test_20230724
(class   varchar(50),name    varchar(50),course  varchar(50),score   INT
);

（二）插入测试数据

插入两个班，四位同学的成绩。

insert into test_20230724 values
('1','张三','语文',80),
('1','李四','数学',95),
('1','张三','数学',90),
('1','李四','英语',80),
('1','王五','数学',70),
('2','小明','语文',80),
('2','小明','数学',70);

三、思路

将该问题拆分，分步处理
第一步，求出平均分低于80分的班级
第二步，平均分低于80分的班级中，求出班级学生各科成绩
第三步，平均分低于80分的班级中，按学生合计总分
第四步，合并第二步和第三步数据

四、解答

（一）详细代码

SELECTclass,name,course,score
FROM test_20230724
WHERE class IN
(SELECT classFROM test_20230724GROUP BY classHAVING AVG(score) < 80
)
UNION ALL 
SELECTclass,name,'合计'         AS course,SUM(score)    AS score
FROM test_20230724
WHERE class in 
(SELECT classFROM test_20230724GROUP BY classHAVING AVG(score) < 80
)
GROUP BY class,name

（二）结果展示

五、知识拓展——使用 grouping 运算符计算小计合计值

grouping 运算符包含以下3种：rollup、cube、grouping sets

（一）rollup

直接算出小计（分组汇总）和合计（整体汇总）

1. 详细代码

SELECTclass,name,course        AS course,SUM(score)    AS score
FROM test_20230724
WHERE class IN
(SELECT classFROM test_20230724GROUP BY classHAVING AVG(score) < 80
)
GROUP BY class,name,course WITH ROLLUP
;

2. 结果展示

如上输出结果截图，有多个NULL值，实际第三行即为2班小明同学成绩小计，第四行即为2班成绩小计，第五行即为整体合计。

3. 代码优化

结合 grouping 替换NULL值，grouping 会将 rollup 分组小计合计产生的行返回 1，否则返回0，可以让我们更好地区分哪些是小计合计导致地NULL值，而非字段值本身为NULL

SELECTGROUPING(class)     AS class,GROUPING(name)      AS name,GROUPING(course)    AS course,SUM(score)          AS score
FROM test_20230724
WHERE class IN
(SELECT classFROM test_20230724GROUP BY classHAVING AVG(score) < 80
)
GROUP BY class,name,course WITH ROLLUP
;

结合空值转换

SELECTIF(GROUPING(class)=1,'班级合计',class)    AS class,IF(GROUPING(name)=1,'学生合计',name)      AS name,IF(GROUPING(course),'得分合计',course)    AS course,SUM(score)    AS score
FROM test_20230724
WHERE class IN
(SELECT classFROM test_20230724GROUP BY classHAVING AVG(score) < 80
)
GROUP BY class,name,course WITH ROLLUP

总结

将复杂的问题拆分成多个简单的问题处理，进而解决复杂问题。类似上文的答题思路，先求平均分低于80分的班级，将其作为子查询，过滤拿到该班级数据，并计算出该班级学生总分，拼接两部分数据就可以得到我们想要的结果。