我不明白为什么我的 group by 失败

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

DDL for STORES:

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

DDL for EMPLOYEES:

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")
/

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

期望的输出：

• 显示每家商店中薪酬最高的员工
• 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 ENAME, MAX(SAL), STORES.CITY FROM EMPLOYEES 
INNER JOIN STORES 
ON EMPLOYEES.STORE_ID = STORES.STORE_ID
GROUP BY ENAME, STORES.CITY

or

SELECT EMPLOYEES.STORE_ID, MAX(SAL), STORES.CITY FROM EMPLOYEES 
INNER JOIN STORES 
ON EMPLOYEES.STORE_ID = STORES.STORE_ID
GROUP BY EMPLOYEES.STORE_ID, STORES.CITY

因为在您的情况下，SQL Select 语句在选择列表中具有非分组和非聚合列，这违反了规则。对于您的目标可能更喜欢以下之一：

SELECT ENAME, MAX(SAL), STORES.STORE_ID, STORES.CITY FROM EMPLOYEES 
INNER JOIN STORES 
ON EMPLOYEES.STORE_ID = STORES.STORE_ID
GROUP BY ENAME, STORES.STORE_ID, STORES.CITY