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 |
±--------------------+
sql语句
/* Write your T-SQL query statement below */
SELECT
-- 判空一下
ISNULL ((
-- 去掉第一个然后拿出最新的一个
SELECT
TOP 1 Salary FROM
Employee
WHERE
Salary not IN (
-- 获取Salary最大值
SELECT MAX(Salary) FROM Employee
)
ORDER BY Salary DESC
),NULL) as SecondHighestSalary