作者: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 |