表名Employee
Id | Saraly |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
预期结果:
SecondHighestSalary |
---|
200 |
MySQL:
select max(Salary) as SecondHighestSalary from (select Salary from Employee where Salary < (select max(Salary) from Employee)) a;
思路分析:
第二高薪水,即去除第一高薪水后的第一高薪水
详细设计:
①查询出第一高薪水
select max(Salary) from Employee;
②将第一高薪水去除后,查询出新的表a
select Salary from Employee where Salary < (select max(Salary) from Employee)) a;
③在表a中查询出最高薪水,列名取名为SecondHighestSalary
select max(Salary) as SecondHighestSalary from (select Salary from Employee where Salary < (select max(Salary) from Employee)) a;