排名第二的学生成绩:
编写一个 SQL 查询,获取Student表中排名第二的学生成绩(Score) 。
ID | Score |
---|---|
1001 | 78 |
1001 | 83 |
1001 | 98 |
例如上述Student表,SQL查询应该返回 98作为排名第二的成绩。如果不存在排名第二的成绩,那么查询应返回 null。
查询结果应如下表所示:
SecondHighestScore |
---|
98 |
MYSQL写法:
SELECT IFNULL((SELECT MAX(Score) FROM Student WHERE Score NOT IN (SELECT MAX(Score) FROM Student)),NULL)as SecondHighestScore
SQL SERVER写法:
SELECT IsNULL((SELECT MAX(Score) FROM Student WHERE Score NOT IN (SELECT MAX(Score) FROM Student)),NULL)as SecondHighestScore
ORACLE写法:
注:NVL()函数
select min(Score) as SecondHighestScore
from(
select Score, dense_rank() over(order by Score desc) rn
from Student
) t
where rn != 1 and rn <= 2