使用Join和窗口函数获取结果

Question

A表Dept

Id   Salary  Dept
1     1000    A
2     2000    B
3     5000    A
4     2500    C
5     3000    D

所以输出结果

Id   Salary Dept
 1   6000   A

需要使用Join或窗口函数获取薪资总和以及哪个部门作为最高薪资

Answer 1

你似乎想要：

select d.dept, sum(d.salary)
from dept d
group by d.dept
order by max(d.salary) desc
fetch first 1 row only

Answer 2

如果您使用的是MSSQL，您可以尝试执行以下操作

select top 1 d.Dept, Sum(d.Salary) as Salary from Dept d group by d.Dept order by Sum(d.Salary) desc