我只需要得到每个城市中收入最高的员工

Question

我正在尝试获得一个新的视图，我可以在其中显示每个商店中最高收入员工的一条记录。

显示每个商店中薪酬最高的员工的输出(所需) STORE_ID -1没有员工，因此不在报告中

ENAME   JOB         STORE_ID    MAX(SAL)    CITY
ALLEN   SALESMAN    2           1600        New York City
KING    PRESIDENT   3           5000        Chicago
SCOTT   ANALYST     4           3000        Philadelphia

实际产出

ENAME   JOB STORE_ID    MAX(SAL)    CITY
ALLEN   SALESMAN    2   1600    New York City
TURNER  SALESMAN    2   1500    New York City
WARD    SALESMAN    2   1250    New York City
MARTIN  SALESMAN    2   1250    New York City
KING    PRESIDENT   3   5000    Chicago
BLAKE   MANAGER 3   2850    Chicago
CLARK   MANAGER 3   2450    Chicago
SCOTT   ANALYST 4   3000    Philadelphia
FORD    ANALYST 4   3000    Philadelphia
JONES   MANAGER 4   2975    Philadelphia
MILLER  CLERK   4   1300    Philadelphia
ADAMS   CLERK   4   1100    Philadelphia
JAMES   CLERK   4   950 Philadelphia
SMITH   CLERK   4   800 Philadelphia

我只能使用SELECT、GROUP BY、HAVING、ORDER BY、INNER JOIN (或其他类型的JOIN)。我不能使用分区，

这个查询确实产生了必要的行，但它包含了所有其他行(除了支付最高的行之外)

商店的DDL

CREATE TABLE  "STORES" 
   (    "STORE_ID" NUMBER NOT NULL ENABLE, 
    "CITY" VARCHAR2(50), 
     PRIMARY KEY ("STORE_ID")
  USING INDEX  ENABLE
   )
/

面向员工的DDL

CREATE TABLE  "EMPLOYEES" 
   (    "EMPNO" NUMBER(4,0), 
    "ENAME" VARCHAR2(10), 
    "JOB" VARCHAR2(9), 
    "HIREDATE" DATE, 
    "SAL" NUMBER(7,2), 
    "COMM" NUMBER(7,2), 
    "STORE_ID" NUMBER
   )
/

CREATE INDEX  "EMP_NAME_IDEX" ON  "EMPLOYEES" ("ENAME")
/

CREATE INDEX  "EMP_NAME_JOB_DATE_IDX" ON  "EMPLOYEES" ("ENAME", "JOB", "HIREDATE")
/

第一个问题是关于糟糕的SQL语法。这个人试图找到每个商店中收入最高的员工。

Answer 1

您可以使用rank窗口函数根据员工的工资对每个商店的员工进行排名，然后只取前一个：

SELECT t.ename, t.job, t.sal, t.city
FROM   (SELECT e.ename, e.job, e.sal, s.city,
               RANK() OVER (PARTITION BY e.store_id ORDER BY s.sal DESC) AS rk
        FROM   employees e
        JOIN   stores s on e.store_id = s.store_id) t
WHERE  rk = 1

Answer 2

Oracle对group by聚合函数进行了很好的扩展，可以提供帮助：

select max(e.ename) keep (dense_rank first order by e.sal desc) as ename,
       max(e.job) keep (dense_rank first order by e.sal desc) as ejob,
       s.store_id,
       max(e.sal) as sal
       s.city
from employee e join
     stores s
     on e.store_id = s.store_id
group by s.store_id, s.city;

Answer 3

尝试像下面这样使用子查询

select e.ENAME,t1.STORE_ID,t1.sal,s.CITY from (
 SELECT  MAX(e.SAL) sal,
 e.STORE_ID FROM EMPLOYEES e    
 GROUP BY e.STORE_ID
       ) t1 join EMPLOYEES e on t1.STORE_ID =e.STORE_ID  and e.sal=t1.sal
            join STORES  s on t1.STORE_ID =s.STORE_ID