【LeetCode】打卡--SQL之176第二高的薪水

【LeetCode】打卡–SQL之176第二高的薪水
题目：
1.自我解答方案

#思路：采用临时表，先把最高工资取出来，然后取出第二高工资
With C1 
AS
    (select Max(Salary) AS Salary 
     from Employee)

SELECT MAX(Salary) AS SecondHighestSalary 
From Employee
Where Salary Not In
                    (Select * 
                     From C1)
##等价于
SELECT MAX(Salary) AS SecondHighestSalary 
From Employee
Where Salary Not In
	(SELECT Max(Salary) AS Salary 
		From Employee)

2.LeetCode平台提供解决方案–LIMIT OFFSET
注意：只有MySQL才有LIMIT方法，MS SQL没有这个方法

思路：采用LIMIT函数跳过第一个
SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
#然而，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，
#因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表。
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
 #等价于
 SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1, 1) AS SecondHighestSalary
  
 #LIMIT和LIMIT OFFSET区别见我博客

3.LeetCode平台提供解决方案–LIMIT OFFSET + IFNULL

#思路：在LIMIT OFFSET上面结合IFNULL函数来解决Null问题
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

我们下次再见，如果还有下次的话！！！
欢迎关注微信公众号：516数据工作室