MYSQLで分析関数(ROW_NUMBER, DENSE_RANK, RANK)を使わずに連番を振る

はじめに

このようなテーブルに対して点数の昇順に連番を振る

氏名	点数
佐藤	80
鈴木	70
高橋	90
田中	80
山田	80

スキーマ

CREATE TABLE 成績表(氏名 varchar(4), 点数 int);
INSERT INTO 成績表(氏名, 点数)
VALUES ('佐藤', 80),
       ('鈴木', 70),
       ('高橋', 90),
       ('田中', 80),
       ('山田', 80);

分析関数の場合

ROW_NUMBER

SELECT ROW_NUMBER() OVER(ORDER BY 点数) AS 番号
     , Z.*
  FROM 成績表 AS Z;

実行結果

番号	氏名	点数
1	鈴木	70
2	佐藤	80
3	山田	80
4	田中	80
5	高橋	90

DENSE_RANK

SELECT DENSE_RANK() OVER(ORDER BY 点数) AS 番号
     , Z.*
  FROM 成績表 AS Z;

実行結果

番号	氏名	点数
1	鈴木	70
2	佐藤	80
2	田中	80
2	山田	80
3	高橋	90

RANK

SELECT RANK() OVER(ORDER BY 点数) AS 番号
     , Z.*
  FROM 成績表 AS Z;

実行結果

番号	氏名	点数
1	鈴木	70
2	佐藤	80
2	田中	80
2	山田	80
5	高橋	90

分析関数を使わない方法

ROW_NUMBERと同じ結果を得る方法

SELECT @rank := @rank + 1 AS 番号
     , Z.*
FROM (SELECT * FROM 成績表 ORDER BY 点数) AS Z
   , (SELECT @rank:=0) AS X;

実行結果

番号	氏名	点数
1	鈴木	70
2	佐藤	80
3	田中	80
4	山田	80
5	高橋	90

DENSE_RANK と同じ結果を得る方法

SELECT @rank :=
         CASE
         WHEN @rankval = 点数 THEN @rank
         ELSE @rank + 1
         END AS 番号
     , @rankval := 点数 AS rankval
     , Z.*
FROM (SELECT * FROM 成績表 ORDER BY 点数) AS Z
   , (SELECT @rank:=0, @rankval:=NULL) AS X;

実行結果

番号	rankval	氏名	点数
1	70	鈴木	70
2	80	佐藤	80
2	80	田中	80
2	80	山田	80
3	90	高橋	90

RANKと同じ結果を得る方法

SELECT @rank :=
         CASE
         WHEN @rankval = 点数 THEN @rank
         ELSE @rank + @gap + 1
         END AS 番号
     , @gap := 
         CASE
         WHEN @rankval = 点数 THEN @gap + 1
         ELSE 0
         END AS gap
     , @rankval := 点数 AS rankval
     , Z.*
FROM (SELECT * FROM 成績表 ORDER BY 点数) AS Z
   , (SELECT @rank:=0, @rankval:=NULL, @gap:=0) AS X;

実行結果

番号	gap	rankval	氏名	点数
1	0	70	鈴木	70
2	0	80	佐藤	80
2	1	80	田中	80
2	2	80	山田	80
5	0	90	高橋	90

お試し

参考記事