sql - 如何获得会计部门第二高的薪水？

我有三个表，如下所示:

Create table #temp (id int, DepartmentName varchar(50))
insert into #temp (id,DepartmentName) values(1,'Account')
insert into #temp (id,DepartmentName) values(2,'IT')
select * from #temp

Create Table #temp1(customerid int, CustomerName varchar(50),DepartmentId int)
Insert into #temp1(customerid,CustomerName,DepartmentId)values(1,'Anil',1)
Insert into #temp1(customerid,CustomerName,DepartmentId)values(2,'Ankit',2)
Insert into #temp1(customerid,CustomerName,DepartmentId)values(3,'Mandeep',1)
Insert into #temp1(customerid,CustomerName,DepartmentId)values(4,'Rajesh',2)
Insert into #temp1(customerid,CustomerName,DepartmentId)values(5,'Rohit',1)
Insert into #temp1(customerid,CustomerName,DepartmentId)values(6,'Sharma',0)

Create Table #temp2(customerid int, salary int)
insert into #temp2(customerid,salary)values(1,2000)
insert into #temp2(customerid,salary)values(3,2399)
insert into #temp2(customerid,salary)values(4,4000)
insert into #temp2(customerid,salary)values(2,4500)
insert into #temp2(customerid,salary)values(5,7000)


select max(t2.salary) ,t.CustomerName,t1.DepartmentName
from #temp1 t
left join #temp t1 on t1.id=t.DepartmentId
left join #temp2 t2 on t2.customerid=t.customerid
where DepartmentName='Account'
and salary<>(select max(tt2.salary)  from #temp2 tt2
inner join #temp1 tt1 on tt1.customerid=tt2.customerid
inner join #temp tt on tt.id=tt1.DepartmentId
where tt.DepartmentName='Account')
group by CustomerName,DepartmentName

但是没有得到第二高的薪水，可以请任何人帮助我，我在外部查询中使用最大聚合函数，但是仍然得到了会计部门的所有薪水。

最佳答案

使用Dense_Rank

SELECT
     DepartmentName,
     CustomerName,
     salary
FROM
    (
    SELECT
        t.DepartmentName,
        DENSE_RANK() OVER( PARTITION BY id ORDER BY salary desc) rno,
        salary,
        t1.CustomerName
    FROM #temp t
    JOIN #temp1 t1
        ON t.id = t1.DepartmentId
    JOIN #temp2 t2
        ON t1.customerid = t2.customerid
    where  t.DepartmentName='Account'
    ) a

WHERE rno = 2

更新
由于我们无法使用客户名称进行分组，因此已使用dense_rank修改了您的查询。

SELECT salary,
       CustomerName,
       DepartmentName
FROM   (SELECT t2.salary,
               t.CustomerName,
               t1.DepartmentName,
               Dense_rank()
                 OVER(
                   partition BY DepartmentName
                   ORDER BY salary DESC) rno
        FROM   #temp1 t
               LEFT JOIN #temp t1
                      ON t1.id = t.DepartmentId
               LEFT JOIN #temp2 t2
                      ON t2.customerid = t.customerid
        WHERE  DepartmentName = 'Account'
               AND salary NOT IN (SELECT Max(tt2.salary)
                                  FROM   #temp2 tt2
                                         INNER JOIN #temp1 tt1
                                                 ON tt1.customerid = tt2.customerid
                                         INNER JOIN #temp tt
                                                 ON tt.id = tt1.DepartmentId
                                  WHERE  tt.DepartmentName = 'Account'))a
WHERE  rno = 1