LeetCode-第二高的薪水

1。题目
Write a SQL query to get the second highest salary from the Employee table.
±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+

2.题解
注意两点：
1.去重，若两个工资相同则排名一样
2.若不存在第二大工资，则返回null
3.若只有两条数据，则第二大就是第一条数据
所以：解题做法就是先把第一大过滤掉，再取剩下中的最大值
题解1：rank法
SELECT max(SecondHighestSalary) SecondHighestSalary
FROM(
SELECT Salary SecondHighestSalary
FROM(
SELECT RANK() OVER (ORDER BY Salary DESC) ORD1, Id, Salary
FROM Employee
)
WHERE ORD1 <> 1
)
题解2：r过滤最大值法
SELECT MAX(Salary) SecondHighestSalary
FROM Employee
where salary < (
SELECT MAX(Salary)
FROM Employee
)