sql - 在单个查询中显示来自 emp 和 dept 表的统计信息

Question

我有两个表 emp 和 dept

Emp 表有以下字段：

EMPNO   ENAME   JOB MGR HIREDATE    SAL COMM    DEPTNO

Dept 表有以下字段：

DEPTNO  DNAME   LOC

规则是：- a。每个部门的员工总数 B. 各部门最高工资 C. 每个部门的最低工资。d。每个部门薪水最高的雇员人数。e. 每个部门薪水最低的雇员人数。F。每个部门薪水最高的员工姓名/身份证。G。每个部门薪水最低的员工姓名/身份证。H。属于每个部门的所有员工的姓名。

o/p 如下：

有关 emp 和 dept 表的数据，请参阅此链接

http://sqlfiddle.com/#!4/1bc2b8

提前致谢..

标签： sqloracle

Answer 1

您可以使用analytical function和GROUP BY如下：

SELECT D.deptno,
       COUNT(1) TOTAL_EMPS,
       MAX(sal) AS MAX_SAL,
       MIN(sal) AS MIN_SAL,
       SUM(CASE WHEN SAL = MINSAL THEN 1 END) EMP_MIN_SAL,
       SUM(CASE WHEN SAL = MAXSAL THEN 1 END) EMP_MAX_SAL,
       MAX(CASE WHEN RNMIN = 1 THEN E.empno END) EMP_WITH_MIN_sAL,
       MAX(CASE WHEN RNMAX = 1 THEN E.empno END) EMP_WITH_MAX_sAL,
       LISTAGG(E.ename, ',') WITHIN GROUP (ORDER BY E.EMPNO) ALL_EMPS
  FROM dept D JOIN (SELECT T.*, 
                           MIN(SAL) OVER (PARTITION BY deptno) MINSAL, 
                           MAX(SAL) OVER (PARTITION BY deptno) MAXSAL, 
                           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY SAL) RNMIN,
                           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY SAL DESC) RNMAX 
                      FROM emp T) E
    ON E.deptno = D.deptno
GROUP BY D.deptno;

SQLFiddle 演示