select last_name, country_name, SUM(salary)
from employees e JOIN departments d ON (e.department_id= d.department_id)
JOIN locations L ON (d.location_id = L.location_id)
JOIN Countries Cc ON (L.country_id = Cc.country_id)
JOIN regions Rr ON (Cc.region_id = Rr.region_id)
GROUP BY country_name;
您发布的代码无法编译;它缺少LAST_NAME
的GROUP BY
(这是基本,错了,因为它会使你在做什么是不可能的),或者-一个更好的主意-从删除SELECT
语句的列列表。
使用RANK
分析功能,您将拥有
WITH data
AS ( SELECT country_name,
SUM (salary) sumsal,
RANK () OVER (ORDER BY SUM (salary) DESC) rn
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations L ON (d.location_id = L.location_id)
JOIN Countries Cc ON (L.country_id = Cc.country_id)
JOIN regions Rr ON (Cc.region_id = Rr.region_id)
GROUP BY country_name)
SELECT country_name, sumsal
FROM data
WHERE rn = 1;
我没有您的表格或数据,因此-为了便于说明-我将使用Scott的示例架构。简化后,它将是这样的:
SQL> select deptno, sum(sal)
2 from emp
3 group by deptno
4 order by 2 desc;
DEPTNO SUM(SAL)
---------- ----------
10 13750 --> this is a department you need
20 10995
30 9400
所以:
SQL> WITH data
2 AS ( SELECT deptno,
3 SUM (sal) sumsal,
4 RANK () OVER (ORDER BY SUM (sal) DESC) rn
5 FROM emp
6 GROUP BY deptno)
7 SELECT deptno, sumsal
8 FROM data
9 WHERE rn = 1;
DEPTNO SUMSAL
---------- ----------
10 13750
SQL>