mysql 实现lead_MySQL变量实现分析函数

作者：kelvin19840813

出处：kelvin19840813 的博客 http://www.cnblogs.com/kelvin19840813/

您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。

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;