MySQL 下 ROW_NUMBER / DENSE_RANK / RANK 的实现

来自：http://blog.sina.com.cn/s/blog_562b10b901011c17.html

CREATE TABLE test_rownum (
ID int,
Ke CHAR(1),
val INT
);

INSERT INTO test_rownum
SELECT 1, 'A', 1 UNION ALL
SELECT 2, 'A', 2 UNION ALL
SELECT 3, 'A', 3 UNION ALL
SELECT 4, 'B', 1 UNION ALL
SELECT 5, 'B', 2 UNION ALL
SELECT 6, 'B', 1 UNION ALL
SELECT 7, 'C', 3 UNION ALL
SELECT 8, 'C', 2 UNION ALL
SELECT 9, 'C', 2;

rownum 用于显示序号
ID 字段用于 标记 原有的序号位置.

普通的处理
等价于 ROW_NUMBER() OVER (ORDER BY ke, val)

SELECT
@rownum:=@rownum+1 AS rownum,
id,
ke,
val
FROM
(SELECT @rownum:=0) r,
test_rownum
ORDER BY
ke, val;

+--------+------+------+------+
| rownum | id | ke | val |
+--------+------+------+------+
| 1 | 1 | A | 1 |
| 2 | 2 | A | 2 |
| 3 | 3 | A | 3 |
| 4 | 4 | B | 1 |
| 5 | 6 | B | 1 |
| 6 | 5 | B | 2 |
| 7 | 8 | C | 2 |
| 8 | 9 | C | 2 |
| 9 | 7 | C | 3 |
+--------+------+------+------+
9 rows in set (0.00 sec)

PARTITION 的处理
等价于 ROW_NUMBER() OVER (PARTITION BY ke ORDER BY val)

SELECT
CASE
WHEN @ke != ke THEN @rownum:= 1
ELSE @rownum:= @rownum + 1
END AS rownum,
id,
@ke := ke AS ke,
val
FROM
(SELECT @ke:='') k,
(SELECT @rownum:=0) r,
test_rownum main
ORDER BY
ke, val;

+--------+------+------+------+
| rownum | id | ke | val |
+--------+------+------+------+
| 1 | 1 | A | 1 |
| 2 | 2 | A | 2 |
| 3 | 3 | A | 3 |
| 1 | 4 | B | 1 |
| 2 | 6 | B | 1 |
| 3 | 5 | B | 2 |
| 1 | 8 | C | 2 |
| 2 | 9 | C | 2 |
| 3 | 7 | C | 3 |
+--------+------+------+------+
9 rows in set (0.00 sec)

RANK DENSE_RANK 的处理
等价于 RANK() OVER (PARTITION BY ke ORDER BY val)

等价于 DENSE_RANK() OVER (PARTITION BY ke ORDER BY val)

SELECT
CASE
WHEN @ke != ke THEN @rownum:= 1
ELSE @rownum:= @rownum + 1
END AS ROW_NUMBER,
CASE
WHEN @ke != ke THEN @rank:= 1
WHEN @val = val THEN @rank
ELSE @rank:= @rownum
END AS RANK,
CASE
WHEN @ke != ke THEN @dense_rank:= 1
WHEN @val = val THEN @dense_rank
ELSE @dense_rank:= @dense_rank + 1
END AS DENSE_RANK,
id,
@ke := ke AS ke,
@val := val AS val
FROM
(SELECT @ke:='') k,
(SELECT @val:=0) v,
(SELECT @rownum:=0) r,
(SELECT @rank:=0) r2,
(SELECT @dense_rank:=0) d,
test_rownum main
ORDER BY
ke, val;

+------------+------+------------+------+------+------+
| ROW_NUMBER | RANK | DENSE_RANK | id | ke | val |
+------------+------+------------+------+------+------+
| 1 | 1 | 1 | 1 | A | 1 |
| 2 | 2 | 2 | 2 | A | 2 |
| 3 | 3 | 3 | 3 | A | 3 |
| 1 | 1 | 1 | 4 | B | 1 |
| 2 | 1 | 1 | 6 | B | 1 |
| 3 | 3 | 2 | 5 | B | 2 |
| 1 | 1 | 1 | 8 | C | 2 |
| 2 | 1 | 1 | 9 | C | 2 |
| 3 | 3 | 2 | 7 | C | 3 |
+------------+------+------------+------+------+------+
9 rows in set (0.00 sec)

=======================================================
两表关联情况下的测试.

CREATE TABLE test_rownum_main (
ID int,
Ke CHAR(1)
);

CREATE TABLE test_rownum_sub (
KeID int,
val INT
);

INSERT INTO test_rownum_main
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C';

INSERT INTO test_rownum_sub
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 3, 2;

普通的处理
等价于 ROW_NUMBER() OVER (ORDER BY ke, val)

SELECT
@rownum:=@rownum+1 AS rownum,
main.id,
main.ke,
sub.val
FROM
test_rownum_main main
JOIN test_rownum_sub sub
ON (main.ID = sub.KeID),
(SELECT @rownum:=0) r
ORDER BY
ke, val;

+--------+------+------+------+
| rownum | id | ke | val |
+--------+------+------+------+
| 1 | 1 | A | 1 |
| 2 | 1 | A | 2 |
| 3 | 1 | A | 3 |
| 4 | 2 | B | 1 |
| 6 | 2 | B | 1 |
| 5 | 2 | B | 2 |
| 8 | 3 | C | 2 |
| 9 | 3 | C | 2 |
| 7 | 3 | C | 3 |
+--------+------+------+------+
9 rows in set (0.00 sec)

rownum 顺序不满足预期结果了。
可能是因为执行关联的时候， 就把 @rownum 计算了。

修改SQL语句.

SELECT
@rownum:=@rownum+1 AS rownum,
id,
ke,
val
FROM
(SELECT @rownum:=0) r,
(
SELECT
main.id,
main.ke,
sub.val
FROM
test_rownum_main main
JOIN test_rownum_sub sub
ON (main.ID = sub.KeID)
) subQuery
ORDER BY
ke, val;

+--------+------+------+------+
| rownum | id | ke | val |
+--------+------+------+------+
| 1 | 1 | A | 1 |
| 2 | 1 | A | 2 |
| 3 | 1 | A | 3 |
| 4 | 2 | B | 1 |
| 5 | 2 | B | 1 |
| 6 | 2 | B | 2 |
| 7 | 3 | C | 2 |
| 8 | 3 | C | 2 |
| 9 | 3 | C | 3 |
+--------+------+------+------+
9 rows in set (0.00 sec)

PARTITION 的处理
等价于 ROW_NUMBER() OVER (PARTITION BY ke ORDER BY val)

SELECT
CASE
WHEN @ke != ke THEN @rownum:= 1
ELSE @rownum:= @rownum + 1
END AS rownum,
id,
@ke := ke AS ke,
val
FROM
(SELECT @ke:='') k,
(SELECT @rownum:=0) r,
(
SELECT
main.id,
main.ke,
sub.val
FROM
test_rownum_main main
JOIN test_rownum_sub sub
ON (main.ID = sub.KeID)
) subQuery
ORDER BY
ke, val;

+--------+------+------+------+
| rownum | id | ke | val |
+--------+------+------+------+
| 1 | 1 | A | 1 |
| 2 | 1 | A | 2 |
| 3 | 1 | A | 3 |
| 1 | 2 | B | 1 |
| 2 | 2 | B | 1 |
| 3 | 2 | B | 2 |
| 1 | 3 | C | 2 |
| 2 | 3 | C | 2 |
| 3 | 3 | C | 3 |
+--------+------+------+------+
9 rows in set (0.00 sec)

DENSE_RANK 的处理

RANK 的处理
等价于 DENSE_RANK() OVER (PARTITION BY ke ORDER BY val)

等价于 RANK() OVER (PARTITION BY ke ORDER BY val)

SELECT
CASE
WHEN @ke != ke THEN @rownum:= 1
ELSE @rownum:= @rownum + 1
END AS ROW_NUMBER,
CASE
WHEN @ke != ke THEN @rank:= 1
WHEN @val = val THEN @rank
ELSE @rank:= @rownum
END AS RANK,
CASE
WHEN @ke != ke THEN @dense_rank:= 1
WHEN @val = val THEN @dense_rank
ELSE @dense_rank:= @dense_rank + 1
END AS DENSE_RANK,
id,
@ke := ke AS ke,
@val := val AS val
FROM
(SELECT @ke:='') k,
(SELECT @val:=0) v,
(SELECT @rownum:=0) r,
(SELECT @rank:=0) r2,
(SELECT @dense_rank:=0) d,
(
SELECT
main.id,
main.ke,
sub.val
FROM
test_rownum_main main
JOIN test_rownum_sub sub
ON (main.ID = sub.KeID)
) subQuery
ORDER BY
ke, val;

+------------+------+------------+------+------+------+
| ROW_NUMBER | RANK | DENSE_RANK | id | ke | val |
+------------+------+------------+------+------+------+
| 1 | 1 | 1 | 1 | A | 1 |
| 2 | 2 | 2 | 1 | A | 2 |
| 3 | 3 | 3 | 1 | A | 3 |
| 1 | 1 | 1 | 2 | B | 1 |
| 2 | 1 | 1 | 2 | B | 1 |
| 3 | 3 | 2 | 2 | B | 2 |
| 1 | 1 | 1 | 3 | C | 2 |
| 2 | 1 | 1 | 3 | C | 2 |
| 3 | 3 | 2 | 3 | C | 3 |
+------------+------+------------+------+------+------+
9 rows in set (0.00 sec)