oracle - Oracle：它在动态视图中给出错误

Question

我有两张桌子，一张是员工，一张是部门。我正在创建按薪水对所有部门进行排名的动态视图。视图应该从 Department 和 Employee 中提取信息，按部门汇总薪水，并按薪水对部门进行排名。

CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(30) NOT NULL
);

CREATE TABLE JOBS
(JOB_ID NUMBER PRIMARY KEY,
JOB_TITLE VARCHAR(35) NOT NULL,
MIN_SALARY DECIMAL NOT NULL,
MAX_SALARY DECIMAL NOT NULL
);

CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR(20) NOT NULL,
LAST_NAME VARCHAR(25) NOT NULL,
EMAIL VARCHAR(25) NOT NULL,
PHONE_NUMBER VARCHAR(20) NOT NULL,
HIRE_DATE DATE NOT NULL,
JOB_ID NUMBER NOT NULL,
SALARY DECIMAL NOT NULL,
DEPARTMENT_ID NUMBER NOT NULL,
CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)
);

INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(1,'IT');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(2,'Sales');

INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES (1,'IT Administrator',250000.00,50000.00);
INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES (2,'Salesman',200000.00,40000.00);

这是我到目前为止创建的，但它给了我一个错误

ORA-00979: 不是 GROUP BY 表达式 00979. 00000 - "不是 GROUP BY 表达式" *原因:
*操作: 行错误: 4 列: 9

这是我的代码

select department_id,department_name,total_salary 
from(

select  department_id,department_name, SALARY, count(*) as total_salary from(
select dep.department_id , dep.department_name ,emp.SALARY,
DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary)
from departments dep

inner join employees emp on dep.DEPARTMENT_ID = emp.DEPARTMENT_ID

)
GROUP BY SALARY)

标签： oracle

Answer 1

您的查询需要将 EMPLOYEES（以获取薪水）加入 DEPARTMENT（以获取 DEPARTMENT_NAME）。通过将员工工资相加计算每个部门的总工资，而不是计算他们。GROUP BY 需要包括非聚合列。

然后，您需要按每个部门的总工资对部门进行排名。此查询对薪水最高的部门进行排名 = 1。它使用左连接来满足没有员工的部门。

select department_id
       , department_name
       , total_salary
       , rank() over (order by total_salary desc) as dept_rank 
from (
     select  d.department_id
             , d.department_name
             , sum(e.SALARY)  as total_salary 
     from department d
          left join employees e
           on e.department_id = d.department_id 
      group by d.department_id
             , d.department_name
     )
/