力扣数据库题目176第二高的薪水
题目
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
+—-+——–+
| Id | Salary |
+—-+——–+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+—-+——–+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
+———————+
| SecondHighestSalary |
+———————+
| 200 |
+———————+
来源：力扣（LeetCode）
方案一
SELECT salary SecondHighestSalary
FROM (
SELECT DISTINCT salary FROM test.employee
UNION ALL
SELECT NULL
UNION ALL
SELECT NULL
) t ORDER BY salary DESC LIMIT 1,1
方案二
SELECT salary SecondHighestSalary
FROM (
SELECT salary,DENSE_RANK() OVER(ORDER BY salary DESC) num
FROM test.employee
UNION ALL
SELECT NULL,2
UNION ALL
SELECT NULL,2
) t
WHERE num = 2
LIMIT 1;
方案三
SELECT t.salary SecondHighestSalary
FROM (
SELECT salary
FROM test.employee
WHERE salary < (
SELECT MAX(salary)
FROM test.employee
)
UNION ALL
SELECT NULL
) t
ORDER BY t.salary DESC LIMIT 1
方案四
SELECT t.salary SecondHighestSalary
FROM (
SELECT salary
FROM test.employee
WHERE salary < ANY(
SELECT salary
FROM test.employee
)
UNION ALL
SELECT NULL
) t
ORDER BY t.salary DESC LIMIT 1
方案五
SELECT (SELECT salary
FROM (
SELECT DISTINCT salary FROM test.employee
) t ORDER BY salary DESC LIMIT 1,1)SecondHighestSalary
方案六
SELECT (SELECT salary
FROM (
SELECT salary,DENSE_RANK() OVER(ORDER BY salary DESC) num
FROM test.employee
) t
WHERE num = 2
LIMIT 1) SecondHighestSalary
方案七
SELECT (SELECT t.salary
FROM (
SELECT salary
FROM test.employee
WHERE salary < (
SELECT MAX(salary)
FROM test.employee
)
) t
ORDER BY t.salary DESC LIMIT 1) SecondHighestSalary
方案八
SELECT (SELECT t.salary
FROM (
SELECT salary
FROM test.employee
WHERE salary < ANY(
SELECT salary
FROM test.employee
)
) t
ORDER BY t.salary DESC LIMIT 1) SecondHighestSalary
分析
题目要求的结果是一行一列，并保证两个条件
1、列别名SecondHighestSalary
2、行至少一行NULL,为了维持这个问题
前四个方案是制造出来一个NULL行，后四个方案是0行使用select （）子查询后造出NULL行
为了保证条件2，废了很多事。
方案一五使用distinct，二六使用的开窗，三七使用的子查询，四八使用的ANY
建表
CREATE TABLE employee (
id int NOT NULL AUTO_INCREMENT,
salary int NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) ENGINE=InnoDB COLLATE=utf8mb4_0900_ai_ci;
数据初始化
INSERT INTO test.employee(id,salary)
VALUES(1,100),(2,200),(3,300)
Original: https://www.cnblogs.com/klarck/p/14085388.html
Author: 一剑破万法
Title: 力扣数据库题目176第二高的薪水
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/591680/
转载文章受原作者版权保护。转载请注明原作者出处！