MySQL实现ROW_NUMBER()/DENSE_RANK()/RANK()

生成组内连续且唯一的数字。类似Oracle的ROW_NUMBER()函数。

SET @rank=0;

SET @deptno=0;
SELECT ename,
sal,
@rank:=IF(@deptno=deptno,@rank+1,1) rank,
@deptno:=deptno deptno
FROM emp
ORDER BY deptno,sal DESC;

+-------------+-----------+------+-----------+
| ename     | sal          | rank | deptno |
+-------------+-----------+------+-----------+
| KING        | 5000.00 |    1   |     10 |
| CLARK    | 2450.00 |    2   |     10 |
| MILLER   | 1300.00 |    3   |     10 |
| FORD      | 3000.00 |    1   |     20 |
| JONES    | 2975.00 |    2   |     20 |
| ADAMS    | 1100.00 |    3   |     20 |
| SMITH      |  800.00 |     4   |     20 |
| BLAKE     | 2850.00 |    1   |     30 |
| ALLEN     | 1600.00 |    2   |     30 |
| TURNER | 1500.00 |    3   |     30 |
| MARTIN   | 1250.00 |    4   |     30 |
| WARD      | 1250.00 |    5   |     30 |
| JAMES     |  950.00   |    6   |     30 |
| SCOTT    | 3000.00 |     1   |     50 |
+------------+------------+-------+-----------+

生成组内连续但不唯一的数字。类似Oracle的DENSE_RANK()函数。

SET @rank=0;

SET @deptno=0;

SET @sal=0;
SELECT ename,
@rank:=IF(@deptno=deptno,IF(@sal=sal,@rank,@rank+1),1) rank,
@deptno:=deptno deptno,
@sal:=sal sal
FROM emp
ORDER BY deptno,sal DESC;

+-------------+--------+------+-----------+
| ename     | rank  | deptno | sal |
+-------------+-------+-------+-----------+
| KING         |    1    |    10  | 5000.00 |
| CLARK     |    2    |    10  | 2450.00 |
| MILLER    |    3    |    10  | 1300.00 |
| FORD       |    1    |    20  | 3000.00 |
| JONES     |    2    |    20  | 2975.00 |
| ADAMS     |    3    |    20  | 1100.00 |
| SMITH      |    4    |    20  |  800.00   |
| BLAKE     |    1    |    30  | 2850.00  |
| ALLEN     |    2    |    30  | 1600.00  |
| TURNER |    3    |    30  | 1500.00  |
| MARTIN   |    4    |    30  | 1250.00  |
| WARD      |    4    |    30  | 1250.00  |
| JAMES     |    5    |    30  |  950.00   |
| SCOTT    |    1    |    50  | 3000.00  |
+------------+-------+-------+-----------+

生成组内既不连续也不唯一的数字。类似Oracle的RANK()函数。

SET @enum=0;
SET @rank=0;

SET @deptno=0;

SET @sal=0;

SELECT @enum:=IF(@deptno=deptno,@enum+1,1) enum,
ename,
@rank:=IF(@deptno=deptno,IF(@sal=sal,@rank,@enum),1) rank,
@deptno:=deptno deptno,
@sal:=sal sal
FROM emp
ORDER BY deptno,sal DESC;

+--------+-----------+------+--------+---------+
| enum | ename  | rank | deptno | sal     |
+--------+-----------+------+--------+---------+
|     1     | KING         |    1 |     10 | 5000.00 |
|     2     | CLARK     |    2 |     10 | 2450.00 |
|     3     | MILLER    |    3 |     10 | 1300.00 |
|     1     | FORD       |    1 |     20 | 3000.00 |
|     2     | JONES     |    2 |     20 | 2975.00 |
|     3     | ADAMS     |    3 |     20 | 1100.00 |
|     4     | SMITH      |    4 |     20 |  800.00 |
|     1     | BLAKE     |    1 |     30 | 2850.00 |
|     2     | ALLEN     |    2 |     30 | 1600.00 |
|     3     | TURNER |    3 |     30 | 1500.00 |
|     4     | MARTIN   |    4 |     30 | 1250.00 |
|     5     | WARD      |    4 |     30 | 1250.00 |
|     6     | JAMES     |    6 |     30 |  950.00 |
|     1     | SCOTT    |    1 |     50 | 3000.00 |
+--------+-----------+------+--------+---------+