时间戳

首页 > 解决方案 > 如何同时从每个部门检索前 3 名最高薪和前 3 名最低薪

mysql - 如何同时从每个部门检索前 3 名最高薪和前 3 名最低薪

问题描述

我想知道是否可以使用 sql oracle 或 mysql 在输出中并排显示每个部门的前 3 名最高薪员工与同一部门的最低 3 名薪水员工。我尝试使用分析函数和相关子查询方法，但只能获得最高或最低，但不能同时获得两者。对此的任何见解都会受到赞赏。

DDL 和 DML：

CREATE TABLE "EMP" ( "EMPNO"      NUMBER(4,0),"ENAME"      VARCHAR2(10 BYTE),"JOB"        VARCHAR2(9 BYTE),"MGR"        NUMBER(4,
 0),"HIREDATE"   DATE,"SAL"        NUMBER(7,2),"COMM"       NUMBER(7,2),"DEPTNO"     NUMBER(2,0),CONSTRAINT "PK_EMP" PRIMARY KEY
 ( "EMPNO" )


Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-11-81','DD-MM-RR'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (8967,'SCOTT','ANALYST',7566,to_date('27-12-19','DD-MM-RR'),3000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-06-81','DD-MM-RR'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-01-82','DD-MM-RR'),1300,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-12-81','DD-MM-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (8900,'SCOTT','ANALYST',7566,to_date('27-12-19','DD-MM-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-04-87','DD-MM-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-04-81','DD-MM-RR'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-05-87','DD-MM-RR'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-12-80','DD-MM-RR'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-05-81','DD-MM-RR'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-02-81','DD-MM-RR'),1600,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-09-81','DD-MM-RR'),1500,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-09-81','DD-MM-RR'),1250,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-02-81','DD-MM-RR'),1250,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-12-81','DD-MM-RR'),950,null,30);

标签： mysqlsqloracleoracle12c

解决方案

一种解决方案是：

WITH t AS
    (SELECT e.*, 
        DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY sal DESC) AS sal_desc,
        DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY sal ASC) AS sal_ASC
    FROM EMP e)
SELECT * 
FROM t
WHERE sal_desc <= 3 OR sal_ASC <= 3

推荐阅读