使用SQL获取按部门分组的最大值

如果聚合查询中只需要一行,可以使用子查询来获取它。

SQL> select * 
2 from (
3       select dept,SUM(salary) sum_salary 
4       from org 
5       group by dept
6       order by sum_salary desc
7      )
8 where rownum <= 1

DEPT       SUM_SALARY
---------- ---------- 
  HW       113000

where 子句,在本例中,包含相关子查询:

select o.*
from org o
where o.salary = (select max(o2.salary) from org o2 where o2.dept = o.dept);

您的查询已接近解决方案。您只需获取薪资值最大的行:

WITH SUMSAL AS (SELECT DEPT, SUM(SALARY) AS SAL FROM ORG GROUP BY DEPT)
SELECT * FROM SUMSAL 
WHERE SAL = (SELECT MAX(SAL) FROM SUMSAL)

如果有多个部门的工资总额最大,这可能会给您多行。