网站独立物流系统查指数

​【题目】

现有“成绩表”，记录了每个学生各科的成绩。表内容如下：

问题：查找单科成绩高于该科目平均成绩的学生名单

【解题思路】

1."查找单科成绩高于该科目平均成绩"，也就是在“每个”科目里比较。还记得我们之前课程里讲过的吗？当有“每个”出现的时候，就要想到是要分组了。

能实现“分组”功能的sql有两种，一是group by字句，另一个是窗口函数的partition by。

2.使用聚合窗口函数(求平均值avg)，将每门课的平均成绩求出以后，然后找出大于比平均成绩的数据。

这就要求分组后不能减少表的行数。

group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

所以这里我们使用使用窗口函数的partition by。

【解题步骤】

第1步，聚合函数avg()作为窗口函数，将每一科目成绩的平均值求出。sql语句如下：

select *,        avg(成绩) over (partition by 科目) as avg_scorefrom 成绩表;

运行结果如下：

第2步，如上表，按科目分组后各科目的平均分已经计算出，接下来只要筛选出成绩大于平均分的数据即可。

那么，只需要在上一步的slq语句里加入条件字句where就可以了

select *,        avg(成绩) over (partition by 科目) as avg_scorefrom 成绩表where 成绩 > avg_score;

很多同学都会用这样的思路解题，但是这样写，sql会报错，为什么呢？

我们在《从零学会sql》里多次强调过，要牢记sql的书写顺序和运行顺序。在运行顺序中，select字句是最后被运行的。

当明白了运行顺序以后，就知道错误的原因了：运行到”where ranking > 2”的时候，因为select字句还没有被执行，因此select中的“ranking”列还没有出现，从而导致报错。

解决方法是什么呢？

这种情况就可以用子查询，也就是把第一步得到查询结果作为一个新的表，sql语句如下：

select *from (select *,              avg(成绩) over (partition by 科目) as avg_score      from 成绩表) as bwhere 成绩 > avg_score;

运行结果如下：

【本题考点】

1.主要考查对窗口函数的灵活使用。

2.在筛选过程中，非常容易因为子查询问题报错，本题也考察了对子查询的熟练运用。

3.本题间接考察了对sql语句执行顺序的熟悉程度。

【举一反三】

在“每个组里比较”的问题，比如查找每个组里大于平均值的数据，可以有两种方法：

1)使用上面讲到的窗口函数来实现

2)使用关联子查询，可以回顾《从零学会sql：复杂查询》里的案例

推荐：如何从零学会SQL？