查找每个部门的emp_names，最高，最低工资和员工人数

Question

我使用 oracle 11g，所以我有 2 个表(员工、部门(：

desc employees:      desc departments
 EMPLOYEE_ID        DEPARTMENT_ID
 FIRST_NAME         DEPARTMENT_NAME
 LAST_NAME          MANAGER_ID
 EMAIL              LOCATION_ID
 PHONE_NUMBER
 HIRE_DATE
 JOB_ID
 SALARY
 COMMISSION_PCT
 MANAGER_ID
 DEPARTMENT_ID

我想得到

employee_name，emp_names，emp_salary，dep_id，dep_names，每人最高工资 DEP 和每个 DEP 的最低工资，以及每个 DEP 的员工人数 部门。

所以我做了这个qouery：

 select FIRST_NAME,DEPARTMENT_ID,max(SALARY),min(SALARY),count(EMPLOYEE_ID)
from employees join departments on employees.department_id = departments.departm
ent_id group by first_name,department_id;

但它给出了一个错误：

第 1 行错误：ORA-00918：列定义不明确

但是我的SQL查询正确吗？

Answer 1

我没有你的表格，所以我用斯科特的表格创建了视图，以模拟你所拥有的。

SQL> create or replace view employees as
  2    select empno  employee_id,
  3           ename  last_name,
  4           deptno department_id,
  5           sal    salary
  6    from emp;
View created.
SQL> create or replace view departments as
  2    select deptno department_id,
  3           dname  department_name
  4    from dept;
View created.
SQL>

以下是我对这个问题的理解：每个部门的员工列表应与其他部门分开(最小值、最大值、计数(。

所以：员工名单：

SQL> select d.department_name, e.last_name
  2  from departments d join employees e on d.department_id = e.department_id
  3  order by d.department_name;
DEPARTMENT_NAM LAST_NAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
RESEARCH       JONES
RESEARCH       FORD
RESEARCH       ADAMS
RESEARCH       SMITH
RESEARCH       SCOTT
SALES          WARD
SALES          TURNER
SALES          ALLEN
SALES          JAMES
SALES          BLAKE
SALES          MARTIN
14 rows selected.

聚合：没有任何员工的部门的外部联接：

SQL> select d.department_name,
  2    min(e.salary) min_sal,
  3    max(e.salary) max_sal,
  4    count(e.employee_id) cnt_emp
  5  from departments d left join employees e on d.department_id = e.department_id
  6  group by d.department_name
  7  order by d.department_name;
DEPARTMENT_NAM    MIN_SAL    MAX_SAL    CNT_EMP
-------------- ---------- ---------- ----------
ACCOUNTING           1300       5000          3
OPERATIONS                                    0
RESEARCH              800       3000          5
SALES                 950       2850          6

不过，LISTAGG允许您在同一语句中列出每个部门的所有员工;请参阅第 5 行。不知何故，我怀疑您是否了解了该功能(当您与此类问题作斗争时(。

SQL> select d.department_name,
  2    min(e.salary) min_sal,
  3    max(e.salary) max_sal,
  4    count(e.employee_id) cnt_emp,
  5    listagg(e.last_name, ', ') within group (order by e.last_name) employees
  6  from departments d left join employees e on d.department_id = e.department_id
  7  group by d.department_name
  8  order by d.department_name;
DEPARTMENT_NAM    MIN_SAL    MAX_SAL    CNT_EMP EMPLOYEES
-------------- ---------- ---------- ---------- -------------------------------------------
ACCOUNTING           1300       5000          3 CLARK, KING, MILLER
OPERATIONS                                    0
RESEARCH              800       3000          5 ADAMS, FORD, JONES, SCOTT, SMITH
SALES                 950       2850          6 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
SQL>