--题目: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 )