2020-04-01-(2) 2020-04-01-(2)

找到Second

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')

输入

{"headers": {"Employee": ["Id", "Salary"]}, "rows": {"Employee": [[1, 100], [2, 200], [3, 300]]}

输出

{"headers": ["SecondHighestSalary"], "values": [[200]]}

Solution 1

Select Max(distinct Salary) as SecondHighestSalary from Employee Where Salary < (Select Max(distinct Salary) from Employee)

使用子查询找到最高salary记为SecondHighestSalary
Max函数返回最大值，distinct去重。

Select Max(distinct Salary) as SecondHighestSalary from Employee

得到第一高薪水。
再找出小于SecondHighestSalary的即是第二高。
Select Max

Select Max(distinct Salary) as SecondHighestSalary from Employee Where Salary < “第一高”

Solution 2 使用 limit 和 offset
limit n子句表示查询结果返回前n条数据
offset n表示跳过x条语句
limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据
使用limit和offset，降序排列再返回第二条记录可以得到第二大的值。