SQL 窗口函数

白日梦想家

发布于 2020-07-18 10:36:05

76000

代码可运行

文章被收录于专栏：SQL实现SQL实现

运行总次数：0

代码可运行

MYSQL 从 8.0.2 版本起开始支持窗口函数，那么在窗口函数没出来之前，我们要实现类似的功能该怎么做呢？

我们先用窗口函数实现一个分组排序的功能，接着再用非窗口函数的方式实现，最后对比这几种实现方式的优劣。

有两个表：emp（员工表）和 dept（表），我们要找出每个部门工资最高的前 2 名（如果出现并列的情况也要列出来）员工的个人信息。

下面是我们用到的表及数据。

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (  empno INT NOT NULL,  ename VARCHAR(10) DEFAULT NULL,  job VARCHAR(9) DEFAULT NULL,  mgr INT DEFAULT NULL,  hiredate DATE DEFAULT NULL,  sal DECIMAL(7,2) DEFAULT NULL,  comm DECIMAL(7,2) DEFAULT NULL,  deptno INT DEFAULT NULL);
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (  deptno INT DEFAULT NULL,  dname VARCHAR(14) DEFAULT NULL,  loc VARCHAR(13) DEFAULT NULL);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17','800.00',NULL,20);INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20','1600.00','300.00',30);INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22','1250.00','500.00',30);INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02','2975.00',NULL,20);INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28','1250.00','1400.00',30);INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01','2850.00',NULL,30);INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09','2450.00',NULL,10);INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'1982-12-09','3000.00',NULL,20);INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,10);INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08','1500.00','0.00',30);INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'1983-01-12','1100.00',NULL,20);INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03','950.00',NULL,30);INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03','3000.00',NULL,20);INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23','1300.00',NULL,10);
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');INSERT INTO dept VALUES (30,'SALES','CHICAGO');INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

我们最终要输出的结果

1. 窗口函数实现

SELECT   empno,  ename,  job,  mgr,  hiredate,  sal,  comm,  deptno FROM  (SELECT     e.*,    rank () over (      PARTITION BY deptno   ORDER BY sal DESC  ) rn   FROM    emp e) t WHERE rn <= 2

2. 自关联实现

SELECT   empno,  ename,  job,  mgr,  hiredate,  sal,  comm,  deptno FROM  (SELECT     a.*,    (SELECT       COUNT(*)     FROM      emp b     WHERE b.deptno = a.deptno       AND a.sal <= b.sal) AS rn   FROM    emp a) e WHERE rn <= 2 ORDER BY deptno,  rn

这种实现方式存在缺陷，当表中有重复数据时，结果就有可能不准确。就拿这个例子来说，如果某个部门里面有 2 人以上的工资并列第一，查询出来的结果没有该部门的数据。

3. 自定义会话变量实现

SELECT   empno,  ename,  job,  mgr,  hiredate,  sal,  comm,  deptno FROM  (SELECT     e.*,    @rn := IF(      @deptno = deptno,      (IF(@sal = sal, @rn, @rn + 1)),      1    ) + @cn AS rn,    @cn := IF(@sal = sal, @cn + 1, 0),    @deptno := deptno,    @sal := sal   FROM    emp e,    (SELECT       @rn := 1,      @cn := 0,      @deptno := NULL,      @sal := NULL) t   ORDER BY deptno,    sal DESC) t WHERE rn <= 2