温馨提示:本文翻译自stackoverflow.com,查看原文请点击:sql - I'm trying to know the country that pays the highest salary from

sql - 我想知道哪个国家的薪水最高

发布于 2020-03-31 23:12:13
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;

查看更多

提问者
Joshua Giwa
被浏览
27
Littlefoot 2020-01-31 19:44

您发布的代码无法编译;它缺少LAST_NAMEGROUP 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>

发布
问题

分享
好友

手机
浏览

扫码手机浏览