作者：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:[email protected],@rn3:[email protected]+1),@rn:=1) as "RANK () OVER (PARTITION BY deptno ORDER BY sal DESC)", if(@sal =sal,@rn2:[email protected] ,if(@deptno = deptno,@rn2:[email protected]+1,@rn2:=1)) as "DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC)", if(@deptno = deptno,@rn:[email protected]+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;
