mysql 之 row_number() over ()

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