SQL实现LeetCode(176.第二高薪水)

[LeetCode] 176.Second Highest Salary 第二高薪水

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 second highest salary is 200. If there is no second highest salary, then the query should return null.

这道题让我们找表中某列第二大的数，这道题有很多种解法，先来看一种使用Limit和Offset两个关键字的解法，MySQL中Limit后面的数字限制了我们返回数据的个数，Offset是偏移量，那么如果我们想找第二高薪水，我们首先可以先对薪水进行降序排列，然后我们将Offset设为1，那么就是从第二个开始，也就是第二高薪水，然后我们将Limit设为1，就是只取出第二高薪水，如果将Limit设为2，那么就将第二高和第三高薪水都取出来：

解法一：

SELECT Salary FROM Employee GROUP BY SalaryUNION ALL (SELECT NULL AS Salary)ORDER BY Salary DESC LIMIT 1 OFFSET 1;

解法二：

SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN(SELECT MAX(Salary) FROM Employee);

解法三：

SELECT MAX(Salary) FROM EmployeeWhere Salary <(SELECT MAX(Salary) FROM Employee);

解法四：

SELECT MAX(Salary) FROM Employee E1WHERE 1 =(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2WHERE E2.Salary > E1.Salary);

https://leetcode.com/discuss/47041/very-very-simple-solution

https://leetcode.com/discuss/42849/general-solution-not-using-max

https://leetcode.com/discuss/21751/simple-query-which-handles-the-null-situation

[LeetCode] 176.Second Highest Salary 第二高薪水

更多相关文章

随机推荐