mysql 之 rank() over()

SET @rn=0;
SET @dense_rank=0;
SET @last_deptno=-1;
SET @last_sal=-1;
SET @ADD = 0;
SELECT deptno,
       empno,
       ename,
       sal,
       IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn,
       IF(@last_sal = sal,@dense_rank := @dense_rank,@dense_rank := @rn) AS 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  | rn   | dense_rank | last_sal | last_deptno |
+--------+-------+--------+------+------+------------+----------+-------------+
|     10 |  7934 | MILLER | 1300 |    1 |          1 |       -1 |          10 |
|     10 |  7782 | CLARK  | 2450 |    2 |          2 |     2450 |          10 |
|     10 |  7839 | KING   | 5000 |    3 |          3 |     5000 |          10 |
|     20 |  7369 | SMITH  |  800 |    1 |          1 |       -1 |          20 |
|     20 |  7876 | ADAMS  | 1100 |    2 |          2 |     1100 |          20 |
|     20 |  7566 | JONES  | 2975 |    3 |          3 |     2975 |          20 |
|     20 |  7788 | SCOTT  | 3000 |    4 |          4 |     3000 |          20 |
|     20 |  7902 | FORD   | 3000 |    5 |          4 |     3000 |          20 |
|     30 |  7900 | JAMES  |  950 |    1 |          1 |       -1 |          30 |
|     30 |  7654 | MARTIN | 1250 |    2 |          2 |     1250 |          30 |
|     30 |  7521 | WARD   | 1250 |    3 |          2 |     1250 |          30 |
|     30 |  7844 | TURNER | 1500 |    4 |          4 |     1500 |          30 |
|     30 |  7499 | ALLEN  | 1600 |    5 |          5 |     1600 |          30 |
|     30 |  7698 | BLAKE  | 2850 |    6 |          6 |     2850 |          30 |
+--------+-------+--------+------+------+------------+----------+-------------+
14 rows in set (0.00 sec)