mysq 之 dense_rank() over()

来源：互联网发布：摄像头改网络编码器编辑：程序博客网时间：2024/06/16 04:08
SET @rn=0;SET @last_deptno=-1;SET @last_sal=-1;SELECT deptno,       empno,       ename,       sal,       IF(@last_deptno = deptno, @rn := @rn + IF(@last_sal = sal,0,1), @rn := 1) AS dense_rank,       IF(@last_deptno = deptno, @last_sal := sal, @last_sal := -1) AS last_sal,       @last_deptno := deptno AS last_deptno  FROM EMP ORDER BY deptno, sal;+--------+-------+--------+------+------------+----------+-------------+| deptno | empno | ename  | sal  | dense_rank | last_sal | last_deptno |+--------+-------+--------+------+------------+----------+-------------+|     10 |  7934 | MILLER | 1300 |          1 |       -1 |          10 ||     10 |  7782 | CLARK  | 2450 |          2 |     2450 |          10 ||     10 |  7839 | KING   | 5000 |          3 |     5000 |          10 ||     20 |  7369 | SMITH  |  800 |          1 |       -1 |          20 ||     20 |  7876 | ADAMS  | 1100 |          2 |     1100 |          20 ||     20 |  7566 | JONES  | 2975 |          3 |     2975 |          20 ||     20 |  7788 | SCOTT  | 3000 |          4 |     3000 |          20 ||     20 |  7902 | FORD   | 3000 |          4 |     3000 |          20 ||     30 |  7900 | JAMES  |  950 |          1 |       -1 |          30 ||     30 |  7654 | MARTIN | 1250 |          2 |     1250 |          30 ||     30 |  7521 | WARD   | 1250 |          2 |     1250 |          30 ||     30 |  7844 | TURNER | 1500 |          3 |     1500 |          30 ||     30 |  7499 | ALLEN  | 1600 |          4 |     1600 |          30 ||     30 |  7698 | BLAKE  | 2850 |          5 |     2850 |          30 |+--------+-------+--------+------+------------+----------+-------------+14 rows in set (0.00 sec)
0 0