mysq 之 dense_rank() over()

最新推荐文章于 2024-08-25 19:55:17 发布

jgmydsai

最新推荐文章于 2024-08-25 19:55:17 发布

阅读量2.5k

点赞数

本文链接：https://blog.csdn.net/jgmydsai/article/details/39997483

版权

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)