【MySQL】变量实现分析函数

来源：互联网发布：图片热点 js 边框编辑：程序博客网时间：2024/04/30 03:57
只收集排版以备查，未验证，请知悉。
原作者： kelvin19840813
出处：http://www.cnblogs.com/kelvin19840813/articles/5701017.html
1. row_number over(order by sal)的实现 Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。 select empno, ename, sal, deptno, @rn := @rn + 1 as rn  from (select empno, ename, sal, deptno          from emp e, (select @rn := 0) b         order by e.sal desc) c; +-------+--------+------+--------+------+ | empno | ename  | sal  | deptno | rn   | +-------+--------+------+--------+------+ |  7839 | KING   | 5000 |     10 |    1 | |  7902 | FORD   | 3000 |     20 |    2 | |  7566 | JONES  | 2975 |     20 |    3 | |  9999 | BLAKE  | 2850 |     10 |    4 | |  7698 | BLAKE  | 2850 |     30 |    5 | |  7782 | CLARK  | 2450 |     10 |    6 | |  7499 | ALLEN  | 1600 |     30 |    7 | |  7844 | TURNER | 1500 |     30 |    8 | |  7934 | MILLER | 1300 |     10 |    9 | |  7654 | MARTIN | 1250 |     30 |   10 | |  7876 | ADAMS  | 1100 |     20 |   11 | |  7900 | JAMES  |  950 |     30 |   12 | |  7369 | SMITH  |  800 |     20 |   13 | +-------+--------+------+--------+------+2. dense_rank over(order by sal)的实现 Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。select empno,       ename,       sal,       deptno,       if(@sal = sal, @rn := @rn, @rn := @rn + 1) as dense_rank,       @sal := sal  from (select empno, ename, sal, deptno          from emp e, (select @rn := 0, @sal := 0) b         order by sal desc) c; +-------+--------+------+--------+------------+-------------+ | empno | ename  | sal  | deptno | dense_rank | @sal := sal | +-------+--------+------+--------+------------+-------------+ |  7839 | KING   | 5000 |     10 | 1          |        5000 | |  7902 | FORD   | 3000 |     20 | 2          |        3000 | |  7566 | JONES  | 2975 |     20 | 3          |        2975 | |  9999 | BLAKE  | 2850 |     10 | 4          |        2850 | |  7698 | BLAKE  | 2850 |     30 | 5          |        2850 | |  7782 | CLARK  | 2450 |     10 | 6          |        2450 | |  7844 | TURNER | 1500 |     30 | 7          |        1500 | |  7934 | MILLER | 1300 |     10 | 8          |        1300 | |  7654 | MARTIN | 1250 |     30 | 9          |        1250 | |  7499 | ALLEN  | 1250 |     30 | 9          |        1250 | |  7876 | ADAMS  | 1100 |     20 | 10         |        1100 | |  7900 | JAMES  |  950 |     30 | 11         |         950 | |  7369 | SMITH  |  800 |     20 | 12         |         800 | +-------+--------+------+--------+------------+-------------+3. rank over(order by sal)的实现 Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的， 同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。 select empno,       ename,       sal,       deptno,       if(@sal = sal, @rn := @rn, @rn := @rn + 1 + @i) as RANK,       if(@sal = sal, @i := @i + 1, @i := 0) as ii,       @sal := sal  from (select empno, ename, sal, deptno          from emp e, (select @rn := 0, @sal = 0, @i := 0) b         order by sal desc) c; +-------+--------+------+--------+------+------+-------------+ | empno | ename  | sal  | deptno | RANK | ii   | @sal := sal | +-------+--------+------+--------+------+------+-------------+ |  7839 | KING   | 5000 |     10 |    1 |    0 |        5000 | |  7902 | FORD   | 3000 |     20 |    2 |    0 |        3000 | |  7566 | JONES  | 2975 |     20 |    3 |    0 |        2975 | |  9999 | BLAKE  | 2850 |     10 |    4 |    0 |        2850 | |  7698 | BLAKE  | 2850 |     30 |    4 |    1 |        2850 | |  7782 | CLARK  | 2450 |     10 |    6 |    0 |        2450 | |  7844 | TURNER | 1500 |     30 |    7 |    0 |        1500 | |  7934 | MILLER | 1300 |     10 |    8 |    0 |        1300 | |  7654 | MARTIN | 1250 |     30 |    9 |    0 |        1250 | |  7499 | ALLEN  | 1250 |     30 |    9 |    1 |        1250 | |  7876 | ADAMS  | 1100 |     20 |   11 |    0 |        1100 | |  7900 | JAMES  |  950 |     30 |   12 |    0 |         950 | |  7369 | SMITH  |  800 |     20 |   13 |    0 |         800 | +-------+--------+------+--------+------+------+-------------+4. row_number over(partition by deptno order by sal desc)的实现 Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。   select empno,       ename,       sal,       deptno,       if(@deptno = deptno, @rn := @rn + 1, @rn := 1) as rn,       @deptno := deptno  from (select empno, ename, sal, deptno          from emp e, (select @rn := 0, @deptno := '') b         order by deptno, sal desc) c;+-------+--------+------+--------+----+-------------------+ | empno | ename  | sal  | deptno | rn | @deptno := deptno | +-------+--------+------+--------+----+-------------------+ |  7839 | KING   | 5000 |     10 |  1 |                10 | |  7782 | CLARK  | 2450 |     10 |  2 |                10 | |  7934 | MILLER | 1300 |     10 |  3 |                10 | |  7902 | FORD   | 3000 |     20 |  1 |                20 | |  7788 | SCOTT  | 3000 |     20 |  2 |                20 | |  7566 | JONES  | 2975 |     20 |  3 |                20 | |  7876 | ADAMS  | 1100 |     20 |  4 |                20 | |  7369 | SMITH  | 800  |     20 |  5 |                20 | |  7698 | BLAKE  | 2850 |     30 |  1 |                30 | |  7499 | ALLEN  | 1600 |     30 |  2 |                30 | |  7844 | TURNER | 1500 |     30 |  3 |                30 | |  7654 | MARTIN | 1250 |     30 |  4 |                30 | |  7521 | WARD   | 1250 |     30 |  5 |                30 | |  7900 | JAMES  | 950  |     30 |  6 |                30 | +-------+--------+------+--------+----+-------------------+5. max/min(sal) over(partition by deptno)的实现: (各部门最高工资) MIN (SAL)KEEP (DENSE_RANK FIRST ORDER BY DEPTNO) OVER (PARTITION BY DEPTNO) MAX (SAL)KEEP (DENSE_RANK LAST ORDER BY DEPTNO) OVER (PARTITION BY DEPTNO)select empno,       ename,       sal,       deptno,       if(@deptno = deptno, @sal := @sal, @sal := sal) as sal_max,       @deptno := deptno  from (select empno, ename, sal, deptno          from emp e, (select @sal := 0, @deptno := '') b         order by deptno, sal desc) c;+-------+--------+------+--------+---------+-------------------+ | empno | ename  | sal  | deptno | sal_max | @deptno := deptno | +-------+--------+------+--------+---------+-------------------+ |  7839 | KING   | 5000 |     10 | 5000.00 |                10 | |  7782 | CLARK  | 2450 |     10 | 5000.00 |                10 | |  7934 | MILLER | 1300 |     10 | 5000.00 |                10 | |  7902 | FORD   | 3000 |     20 | 3000.00 |                20 | |  7788 | SCOTT  | 3000 |     20 | 3000.00 |                20 | |  7566 | JONES  | 2975 |     20 | 3000.00 |                20 | |  7876 | ADAMS  | 1100 |     20 | 3000.00 |                20 | |  7369 | SMITH  | 800  |     20 | 3000.00 |                20 | |  7698 | BLAKE  | 2850 |     30 | 2850.00 |                30 | |  7499 | ALLEN  | 1600 |     30 | 2850.00 |                30 | |  7844 | TURNER | 1500 |     30 | 2850.00 |                30 | |  7654 | MARTIN | 1250 |     30 | 2850.00 |                30 | |  7521 | WARD   | 1250 |     30 | 2850.00 |                30 | |  7900 | JAMES  | 950  |     30 | 2850.00 |                30 | +-------+--------+------+--------+---------+-------------------+6. sum(sal) over(partition by deptno)的实现 方法 : (各部门工资汇总) select empno,       ename,       sal,       deptno,       if(@deptno = deptno, @sal_s := @sal_s, @sal_s := sal_sum) as sal_sum,       @deptno := deptno  from (select empno,               ename,               sal,               deptno,               if(@deptno = deptno, @sal_s := @sal_s + sal, @sal_s := sal) as sal_sum,               @deptno := deptno          from (select empno, ename, sal, deptno                  from emp e, (select @sal_s := 0, @deptno := '') b                 order by deptno) c         order by deptno, sal_sum desc) d;+-------+--------+------+--------+---------+-------------------+ | empno | ename  | sal  | deptno | sal_sum | @deptno := deptno | +-------+--------+------+--------+---------+-------------------+ |  7782 | CLARK  | 2450 |     10 | 8750    |                10 | |  7839 | KING   | 5000 |     10 | 8750    |                10 | |  7934 | MILLER | 1300 |     10 | 8750    |                10 | |  7369 | SMITH  | 800  |     20 | 10875   |                20 | |  7566 | JONES  | 2975 |     20 | 10875   |                20 | |  7788 | SCOTT  | 3000 |     20 | 10875   |                20 | |  7876 | ADAMS  | 1100 |     20 | 10875   |                20 | |  7902 | FORD   | 3000 |     20 | 10875   |                20 | |  7499 | ALLEN  | 1600 |     30 | 9400    |                30 | |  7900 | JAMES  | 950  |     30 | 9400    |                30 | |  7521 | WARD   | 1250 |     30 | 9400    |                30 | |  7844 | TURNER | 1500 |     30 | 9400    |                30 | |  7654 | MARTIN | 1250 |     30 | 9400    |                30 | |  7698 | BLAKE  | 2850 |     30 | 9400    |                30 | +-------+--------+------+--------+---------+-------------------+7. sum(sal) over(partition by deptno order by hiredate)的实现 select empno,       ename,       sal,       deptno,       if(@deptno = deptno, @sal_s := @sal_s + sal, @sal_s := sal) as sal_sum,       @deptno := deptno  from (select empno, ename, sal, deptno          from emp e, (select @sal_s := 0, @deptno := '') b         order by deptno, hiredate) c;+-------+--------+------+--------+---------+-------------------+ | empno | ename  | sal  | deptno | sal_sum | @deptno := deptno | +-------+--------+------+--------+---------+-------------------+ |  7782 | CLARK  | 2450 |     10 |    2450 |                10 | |  7839 | KING   | 5000 |     10 |    7450 |                10 | |  7934 | MILLER | 1300 |     10 |    8750 |                10 | |  7369 | SMITH  | 800  |     20 |     800 |                20 | |  7566 | JONES  | 2975 |     20 |    3775 |                20 | |  7902 | FORD   | 3000 |     20 |    6775 |                20 | |  7788 | SCOTT  | 3000 |     20 |    9775 |                20 | |  7876 | ADAMS  | 1100 |     20 |   10875 |                20 | |  7499 | ALLEN  | 1600 |     30 |    1600 |                30 | |  7521 | WARD   | 1250 |     30 |    2850 |                30 | |  7698 | BLAKE  | 2850 |     30 |    5700 |                30 | |  7844 | TURNER | 1500 |     30 |    7200 |                30 | |  7654 | MARTIN | 1250 |     30 |    8450 |                30 | |  7900 | JAMES  | 950  |     30 |    9400 |                30 | +-------+--------+------+--------+---------+-------------------+sum(sal) over(partition by deptno order by hiredate)的实现 方法2: select *  from (select e.*, @var := @var + sal as group_sum_salary          from emp e, (select @var := 0) c         order by deptno, sal) a order by deptno, group_sum_salary;+-------+--------+-----------+------+---------------------+---------+---------+--------+------------------+ | EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL     | COMM    | DEPTNO | group_sum_salary | +-------+--------+-----------+------+---------------------+---------+---------+--------+------------------+ |  7934 | MILLER | CLERK     | 7782 | 0000-00-00 00:00:00 | 1300.00 |    NULL |     10 |             1300 | |  7782 | CLARK  | MANAGER   | 7839 | 0000-00-00 00:00:00 | 2450.00 |    NULL |     10 |             3750 | |  7839 | KING   | PRESIDENT | NULL | 0000-00-00 00:00:00 | 5000.00 |    NULL |     10 |             8750 | |  7369 | SMITH  | CLERK     | 7902 | 0000-00-00 00:00:00 |  800.00 |    NULL |     20 |             9550 | |  7876 | ADAMS  | CLERK     | 7788 | 0000-00-00 00:00:00 | 1100.00 |    NULL |     20 |            10650 | |  7566 | JONES  | MANAGER   | 7839 | 0000-00-00 00:00:00 | 2975.00 |    NULL |     20 |            13625 | |  7788 | SCOTT  | ANALYST   | 7566 | 0000-00-00 00:00:00 | 3000.00 |    NULL |     20 |            16625 | |  7902 | FORD   | ANALYST   | 7566 | 0000-00-00 00:00:00 | 3000.00 |    NULL |     20 |            19625 | |  7900 | JAMES  | CLERK     | 7698 | 0000-00-00 00:00:00 |  950.00 |    NULL |     30 |            20575 | |  7654 | MARTIN | SALESMAN  | 7698 | 0000-00-00 00:00:00 | 1250.00 | 1400.00 |     30 |            21825 | |  7521 | WARD   | SALESMAN  | 7698 | 0000-00-00 00:00:00 | 1250.00 |  500.00 |     30 |            23075 | |  7844 | TURNER | SALESMAN  | 7698 | 0000-00-00 00:00:00 | 1500.00 |    0.00 |     30 |            24575 | |  7499 | ALLEN  | SALESMAN  | 7698 | 0000-00-00 00:00:00 | 1600.00 |  300.00 |     30 |            26175 | |  7698 | BLAKE  | MANAGER   | 7839 | 0000-00-00 00:00:00 | 2850.00 |    NULL |     30 |            29025 | +-------+--------+-----------+------+---------------------+---------+---------+--------+------------------+sum(sal) over(partition by deptno) 和 sum(sal) over(partition by deptno order by hiredate) 组合输出:  select e.*,       if(@deptno = e.deptno, @salary, @salary := sum_salary) as group_sum_salary,       @deptno := e.deptno  from (select e.*          from (select e.*,                       if(@var = e.deptno, @sal := @sal + sal, @sal := sal) as sum_salary,                       @var := e.deptno                  from emp e,                       (select @var    := 0,                               @sal    := 0,                               @deptno := 0,                               @salary := 0) c                 order by e.deptno, e.sal) e         order by e.deptno, e.sum_salary desc) e;+-------+--------+-----------+------+------------+------+------+--------+------------+------------------+------------------+---------------------+ | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | sum_salary | @var := e.deptno | group_sum_salary | @deptno := e.deptno | +-------+--------+-----------+------+------------+------+------+--------+------------+------------------+------------------+---------------------+ |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |       8750 |               10 | 8750             |                  10 | |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |       3750 |               10 | 8750             |                  10 | |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |       1300 |               10 | 8750             |                  10 | |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |      10875 |               20 | 10875            |                  20 | |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 | NULL |     20 |       7875 |               20 | 10875            |                  20 | |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |       4875 |               20 | 10875            |                  20 | |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 | NULL |     20 |       1900 |               20 | 10875            |                  20 | |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800  | NULL |     20 |        800 |               20 | 10875            |                  20 | |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |       9400 |               30 | 9400             |                  30 | |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 | 300  |     30 |       6550 |               30 | 9400             |                  30 | |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 | 0    |     30 |       4950 |               30 | 9400             |                  30 | |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 | 500  |     30 |       3450 |               30 | 9400             |                  30 | |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |       2200 |               30 | 9400             |                  30 | |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 950  | NULL |     30 |        950 |               30 | 9400             |                  30 | +-------+--------+-----------+------+------------+------+------+--------+------------+------------------+------------------+---------------------+8. lag & lead (sal) over(order by hiredate)的实现 SELECT empno,       ename,       sal,       deptno,       IF(@sal is not NULL, @sal := @sal, @sal := NULL) AS ename_lag,       @sal := sal  FROM emp, (SELECT @sal := NULL) AS a order by hiredate;+-------+--------+------+--------+-----------+-------------+ | empno | ename  | sal  | deptno | ename_lag | @sal := sal | +-------+--------+------+--------+-----------+-------------+ |  7369 | SMITH  | 800  |     20 | NULL      | 800.00      | |  7499 | ALLEN  | 1600 |     30 | 800       | 1600.00     | |  7521 | WARD   | 1250 |     30 | 1600      | 1250.00     | |  7566 | JONES  | 2975 |     20 | 1250      | 2975.00     | |  7698 | BLAKE  | 2850 |     30 | 2975      | 2850.00     | |  7782 | CLARK  | 2450 |     10 | 2850      | 2450.00     | |  7844 | TURNER | 1500 |     30 | 2450      | 1500.00     | |  7654 | MARTIN | 1250 |     30 | 1500      | 1250.00     | |  7839 | KING   | 5000 |     10 | 1250      | 5000.00     | |  7902 | FORD   | 3000 |     20 | 5000      | 3000.00     | |  7900 | JAMES  | 950  |     30 | 3000      | 950.00      | |  7934 | MILLER | 1300 |     10 | 950       | 1300.00     | |  7788 | SCOTT  | 3000 |     20 | 1300      | 3000.00     | |  7876 | ADAMS  | 1100 |     20 | 3000      | 1100.00     | +-------+--------+------+--------+-----------+-------------+ select e.*, @mx, @mx := sal as LAG  from emp e, (select @mx := '') c order by e.sal desc;+-------+--------+-----------+------+------------+------+------+--------+---------+---------+ | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | @mx     | LAG     | +-------+--------+-----------+------+------------+------+------+--------+---------+---------+ |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |         | 5000.00 | |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 | NULL |     20 | 5000.00 | 3000.00 | |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 | 3000.00 | 3000.00 | |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 | 3000.00 | 2975.00 | |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 | 2975.00 | 2850.00 | |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 | 2850.00 | 2450.00 | |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 | 300  |     30 | 2450.00 | 1600.00 | |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 | 0    |     30 | 1600.00 | 1500.00 | |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 | 1500.00 | 1300.00 | |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 | 1300.00 | 1250.00 | |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 | 500  |     30 | 1250.00 | 1250.00 | |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 | NULL |     20 | 1250.00 | 1100.00 | |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 950  | NULL |     30 | 1100.00 | 950.00  | |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800  | NULL |     20 | 950.00  | 800.00  | +-------+--------+-----------+------+------------+------+------+--------+---------+---------+select *  from (select e.*, cast(@mx as UNSIGNED) as lead, @mx := sal as var          from emp e, (select @mx := NULL) c         order by e.sal desc) c order by c.sal;9. RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC)select empno,       ename,       sal,       deptno,       if(@deptno = deptno,          if(@sal = sal, @rn := @rn, @rn3 := @rn3 + 1),          @rn := 1) as "RANK () OVER (PARTITION BY deptno ORDER BY sal DESC)",       if(@sal = sal,          @rn2 := @rn2,          if(@deptno = deptno, @rn2 := @rn2 + 1, @rn2 := 1)) as "DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC)",       if(@deptno = deptno, @rn := @rn + 1, @rn := 1) as "ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC)",       @deptno := deptno,       @sal := sal  from (select empno, ename, sal, deptno          from emp a,               (select @rn     := 1,                       @deptno := 0,                       @rn2    := 0,                       @rn3    := 0,                       @sal    := 0,                       @i      := 0) b         order by deptno, sal desc) c;
0 0