mysql实现dense_rank_MySQL 下 ROW_NUMBER / DENSE_RANK / RANK 的实现

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)