0
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

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

お試し

参考記事

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
0
Help us understand the problem. What are the problem?