SQL架构
Create table If Not Exists Employee (Id int, Salary int)
Truncate table Employee
insert into Employee (Id, Salary) values ('1', '100')
insert into Employee (Id, Salary) values ('2', '200')
insert into Employee (Id, Salary) values ('3', '300')
题目描述
编写一个 SQL 查询,获取 Employee
表中第二高的薪水(Salary) 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee
表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
解法
思路:获取第二高的薪水,不存在第二高的薪水则返回null。
分析:什么情况下可能没有存在第二高薪水,该返回为null呢?
情况1:只有一行记录,那么就没有第二高的薪水。
情况2:有两行记录,这两行记录的薪水数值一样,也就不存在有第二高薪水。
以上两种情况需要返回为null,否则就直接返回第二高薪水。
方法1:ORDER BY + IFNULL + LIMIT + DISTINCT
解题步骤:
1.首先我们先对薪水进行排序(降序)。
SELECT Salary AS SecondHighestSalary
FROM Employee
ORDER BY Salary DESC
结果:
{"headers":["Salary"],"values":[[300],[200],[100]]}
ORDER BY 列名 [DESC]:对指定的列名进行排序,默认为升序,DESC
为降序。
2.我们获取排序好的的第二行记录就可以得到第二高薪水了。
SELECT Salary AS SecondHighestSalary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
结果:
{"headers":["Salary"],"values":[[200]]}
LIMIT 行数 OFFSET 位置偏移量 :在指定偏移量位置读取指定行数。
3.那如果像情况1那样,只有一行记录呢? 也就是没有第二高薪水。可以使用IFNULL
函数来解决。
SELECT IFNULL(
(SELECT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1)
,NULL) AS SecondHighestSalary;
IFNULL(expression, value):IFNULL
函数如果第一个参数的表达式 expression 为 NULL,则返回第二个参数的备用值。
例如:
SELECT IFNULL(NULL, "RUNOOB");
返回:
RUNOOB
3.那如果像情况2那样,有两行记录,但记录重复呢? 也就是没有第二高薪水。可以使用DISTINCT
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1)
,NULL) AS SecondHighestSalary;
SELECT DISTINCT 列名 :关键词 DISTINCT
用于返回唯一不同的值。忽略重复的值。
耗时:202ms
方法2: GROUP BY + ORDER BY + IFNULL + LIMIT
SELECT IFNULL(
(SELECT Salary FROM Employee
GROUP BY Salary
ORDER BY Salary DESC
LIMIT 1 OFFSET 1)
,NULL) AS SecondHighestSalary
ORDER BY 列名: 对指定列名进行分组。
耗时:100ms
方法3:使用子查询+MAX函数
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (
SELECT MAX(Salary)
FROM Employee
)
MAX(列名): MAX 函数返回一列中的最大值。NULL 值不包括在计算中。