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

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

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

select *  FROM [Employee]--等于2时为空,因为有并列第一
SELECT name from (select Name, RANK() over (order by salary desc) as rankIndex
FROM [Test].[dbo].[Employee]) as temp
where 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 )