Sql 工资第二高(考虑并列)

来源：互联网发布：sure target mac插件编辑：程序博客网时间：2024/04/29 03:53

--题目:Employee表中有ID,Name,Salary三列,求薪资排序第二高的Employee的Name

select *  FROM [Employee]--等于2时为空,因为有并列第一SELECT name from (select Name, RANK() over (order by salary desc) as rankIndexFROM [Test].[dbo].[Employee]) as tempwhere temp.rankIndex=2-- 先找出前两高,然后找出第二高select name from Employee where Salary=(   select top 1 salary from   (select distinct top 2  salary from Employee order by Salary desc) as temp1    order by Salary)-- 借助Max函数,先找出最大,然后在在排除最大的数据量找最大select  Name from [Employee]  where Salary =(select MAX(Salary) from [Employee]  where Salary not in (select MAX(Salary) from [Employee]))--先找出前两高,然后借助min函数找出最小值,即为第二高select  Name from [Employee]  where Salary =(select min(Salary) from (select distinct top 2  salary from Employee order by Salary desc) as temp1 )

如图:

0 0