SQL每日一练（7）

继续做一下题目，这次是连续两道题目连在一起的，就一起做了。

原始表scores

class	course	score
Class1	data_visualization	85
Class1	data_visualization	90
Class2	data_visualization	80
Class2	data_visualization	75
Class1	sql	78
Class1	sql	82
Class2	sql	70
Class2	sql	72

题目一：
查询 Class1 班级 data_visualization 平均分与 Class2 中 data_visualization 平均分的差值。
结果输出：Class1_data_visualization 平均分，Class2_data_visualization 平均分，差值。

题目二：
Class1 班级 data_visualization 平均分与 Class2 中 data_visualization 平均分的差值（四舍五入保留 2 位小数）？
填写示例：2.11

题目一思路：（考察avg还有case when then end）

平均数利用avg函数即可 查询中加入case data_visualization when score即可，差值直接计算两个平均数差值即可。

SELECTAVG(CASE WHEN class = 'Class1' AND course = 'data_visualization' THEN score ELSE NULL END) AS avg_class1_data_visualization,AVG(CASE WHEN class = 'Class2' AND course = 'data_visualization' THEN score ELSE NULL END) AS avg_class2_data_visualization,AVG(CASE WHEN class = 'Class1' AND course = 'data_visualization' THEN score ELSE NULL END) - AVG(CASE WHEN class = 'Class2' AND course = 'data_visualization' THEN score ELSE NULL END) AS diff
FROMscores;

题目二思路：（考察round函数）round（需要的值，四舍五入保留位数）

直接round即可

SELECT ROUND(AVG(CASE WHEN class = 'Class1' AND course = 'data_visualization' THEN score ELSE NULL END) - AVG(CASE WHEN class = 'Class2' AND course = 'data_visualization' THEN score ELSE NULL END), 2) AS difference
FROM scores;

 这里提一下我们是把where优化掉了，可以加where course省略掉case 里面的课程号，但是我们这样更好，少了一次查询。

题目三：
查询各班级 sql 成绩中低于于班级 sql 平均分的人员个数。
结果输出：班级，班级 sql 平均分，低于 sql 成绩平均分人数。

题目四：
班级 sql 平均分最低的班级是哪个？对应的低于 sql 成绩平均分人数是多少？
填写示例：Class1 5

题目三思路：利用sum窗口函数+（partition即可）

SELECT class,AVG(score) AS avg_sql,  -- 计算班级SQL平均分（聚合函数）SUM(CASE WHEN score < AVG(score) OVER (PARTITION BY class) THEN 1 ELSE 0 END) AS num_people
FROM scores
WHERE course = 'sql'  -- 过滤SQL课程
GROUP BY class;  -- 按班级分组统计

 题目四：order 排序limit1 然后查询人数即可

SELECT class,AVG(score) AS avg_sql,SUM(CASE WHEN score < AVG(score) OVER (PARTITION BY class) THEN 1 ELSE 0 END) AS num_people
FROM scores
WHERE course = 'sql'
GROUP BY class
ORDER BY avg_sql ASC
LIMIT 1;