面试题8 第二高的查询思路/查询第N高的数据

 面试题8 第二高的查询思路/查询第N高的数据

【题目】下表所示“成绩表”记录了学生选修课程的名称及成绩。现在需要找出语文课中成绩排名第二的学生成绩。如果不存在第二名成绩的学生，那么查询应返回null。

【解题思路】可以把问题拆解为以下三步。

1 、找出所有选修了语文课的学生的成绩。

select *

from 成绩表

where 课程=‘语文’;

2 、在语文课的成绩中，找出排名第二的学生成绩。

思路1：使用子查询

• ①考虑到成绩可能有一样的值，所以使用关键字distinct对成绩进行去重，后得到语文课的最大值。

select max(distinct 成绩) 

from 成绩表

where 课程=‘语文’;

• ②把第①步的查询结果（最高的成绩）记为a（给子查询用as关键字起个别名叫作a，方便在SQL的其他地方使用）​，然后找出小于a的所有成绩，SQL语句的书写方法如下：

成绩 < 

        （select max(distinct 成绩)

            from 成绩表

            where 课程=‘语文’);  

• ③在小于a的所有成绩中，最大值就是课程成绩排在第二名的值。把上述SQL语句合并在一起就是最终答案。需要注意，在条件语句中编写子查询时，不能包含别名。这是因为该子查询会被当作单个值而不是一个表，最终SQL语句如下所示：

select max(distinct 成绩) 

from 成绩表

where 课程=‘语文’ and 成绩 < 

        （select max(distinct 成绩)

            from 成绩表

            where 课程=‘语文’);        

思路2：使用 limit(返回前n条数据) 和 offset(跳过n条语句)，limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据

select distinct 成绩  

from 成绩表

where 课程='语文'

order by 课程,成绩 desc

limit 1,1;

3、如果没有第二高的成绩，返回空值，所以这里用判断空值的函数（ifnull）函数来处理特殊情况。

ifnull(a,b)函数解释：

如果value1不是空，结果返回a

如果value1是空，结果返回b

select ifnull(第2步的sql,null) as '语文课第二名成绩';

4、最终查询语句

select ifnull(

(select max(distinct 成绩) 
from 成绩表
where 成绩<(select max(成绩)  
           from 成绩表  
           where 课程='语文') 
and 课程='语文')

,null) as '语文课第二名成绩';

结果：

【本题考点】

• (1)汇总函数（最大值max()）的用法。
• (2)去掉重复数据关键字distinct的用法。
• (3)子查询的用法，子查询经常被当作中间结果的临时表来使用。嵌套的SQL查询语句用括号括起来，叫作子查询。为了方便使用子查询，一般会用as关键字给子查询起个别名。子查询还可以结合逻辑运算符in、any、all，从而构建复杂的查询。
• limit的用法
• isfull的用法

in（子查询）in常用于where子句中，表示查询某个范围内的数据。in和子查询结合在一起的用法是：in（子查询）​。通过下面的面试题，我们来学习如何应用in（子查询）解决实际问题。

【举一反三】

查找 Employee 表中第二高的薪水（Salary）。查询结果返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

select ifNull(

(select distinct salary

from Employee

order by Salary Desc

limit 1,1),null) as SecondHighestSalary;