取出排名第N的值
题目:
答案:
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM
(SELECT Salary,DENSE_RANK() OVER(ORDER BY Salary DESC) AS rk
FROM Employee) e1
WHERE rk = 2),NULL) AS SecondHighestSalary
注意点:
1. IFNULL的使用:可以使用select语句进行转换,但空值应直接写在select中而非from中,例如,以下这种写法,如果第二高真的是空值,则返回空而不是Null
SELECT IFNULL(Salary,NULL) AS SecondHighestSalary
FROM
(SELECT DISTINCT Salary
FROM
(SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS rk
FROM Employee) a
WHERE rk = 2) b
2. 正确的那段使用了子查询,其中:
1)这种子查询在Mysql环境不需要FROM语句
2)只能输出1个结果,多行数据会报错