我不明白为什么我的团队失败了 [英] I don't understand why my group by is failing
问题描述
SELECT ENAME, MAX(SAL), STORES.CITY
FROM EMPLOYEES
INNER JOIN STORES ON EMPLOYEES.STORE_ID = STORES.STORE_ID
GROUP BY EMPLOYEES.STORE_ID, STORES.CITY
STORES的DDL:
CREATE TABLE "STORES"
(
"STORE_ID" NUMBER NOT NULL ENABLE,
"CITY" VARCHAR2(50),
PRIMARY KEY ("STORE_ID")
USING INDEX ENABLE
)
/
EMPLOYEES的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")
/
我正在尝试获取一个新视图，以便在其中显示每个商店中薪水最高的员工的记录.
I am trying to get a new view where I can display the record for the highest paid employee in each store.
所需的输出:
- 显示每个商店中TOP薪水最高的员工
- 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
推荐答案
正如其他人已经解释的那样，你不能吃蛋糕和吃它."
您可以对某些东西进行 by 分组，可以按原样返回它，也可以进行汇总.在您的情况下，您想按商店分组，即每个商店有一个单独的结果行(与内部加入条件匹配)，而不是按员工.在这种情况下，必须汇总员工数据，其中包括姓名.
经典汇总会占用组中的所有条目，因此您要使用汇总中的所有员工姓名，而这并不是您想要的，因为您只想要薪水最高的员工.
As others have already explained, "You can't have your cake and eat it".
You either group by something, and can return it as is, or have something aggregated. In your case you want to group by store, i.e. have a separate result row per store (that matches the inner join criteria), but not by employee. In that case the employee data has to be aggregated, which includes name.
A classic aggregation takes all entries within a group, so you would use all employee names in the aggregation, which is not what you want, as you want just the employee with the top salary.
幸运的是，Oracle DB在这种情况下确实具有某些功能(尽管可能不是全部)，那就是
Fortunately Oracle DB does have something for such cases (although possibly not all), and that is the KEEP modifier of aggregate functions, with its DENSE_RANK and LAST / FIRST.
This construct allows the aggregation to be performed on a subset of rows from the group, where what you KEEP in the aggregation is FIRST or LAST in a designated ranking (based on given ordering criteria). In your case, you'd need to order employees within the group (employees of a store) based on salary criterion and that way have the aggregation limited to the best paid employees. Assuming, that salaries are unique values (which they are usually not), this will give you a sub-group of one employee, so you can apply any aggregation you want (often MAX or MIN), and still get that one name. If we'll assume, that salary values are not unique, then there may be more than one employee with the top salary - there's a tie on the first place. In that case you either introduce a tie-breaker into the ranking order (e.g. the secondary criteria, after salary, can be the job name) or resolve the issue at the aggregation level (e.g. by choosing the MAX or MIN employee name).
总而言之，我们得到这样的东西:
All in all we get something like this:
SELECT MAX(e.ename)
KEEP (DENSE_RANK FIRST ORDER BY e.sal DESC NULLS LAST, e.job) AS name,
MIN(e.job)
KEEP (DENSE_RANK FIRST ORDER BY e.sal DESC NULLS LAST, e.job) AS job,
e.store_id,
MAX(e.sal) AS salary,
s.city
FROM employees e
INNER JOIN stores s ON e.store_id = s.store_id
GROUP BY e.store_id, s.sity
这篇关于我不明白为什么我的团队失败了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
