MySQLのSELECTステートメントで行インデックス（ランク）を生成する方法

ROW_NUMBER()を使って行番号（ランク）を生成する

MySQLでSELECTステートメントの結果に行インデックス（ランク）を付与したい場合は、ウィンドウ関数のROW_NUMBER()を使用します。この関数はPARTITION BY句と組み合わせることで、グループごとに1から順に連番を振ることができ、重複データに順位を付けたい場合に特に便利です。

ここでは、重複する名前ごとにランクを割り当てる手順を具体的に見ていきましょう。まずはサンプル用のテーブルを作成します。

mysql> create table DemoTable
(
   Name varchar(40)
);
Query OK, 0 rows affected (0.49 sec)

テーブルへのレコード挿入

次に、INSERTコマンドを使って複数のレコードを挿入します。意図的に同じ名前を複数回登録している点に注目してください。

mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.08 sec)

登録済みレコードの確認

SELECTステートメントでテーブル内のすべてのレコードを表示してみます。

mysql> select *from DemoTable;

実行すると、次のような出力が得られます。

+--------+
| Name   |
+--------+
| Chris  |
| Chris  |
| Chris  |
| Robert |
| Robert |
| Adam   |
| Adam   |
| Adam   |
| Adam   |
+--------+
9 rows in set (0.00 sec)

ROW_NUMBER()でランクを生成するクエリ

以下が、SELECTステートメント内で行インデックスを生成するクエリです。PARTITION BY Nameを指定することで、同じ名前を持つ行ごとに独立した連番が振られます。

mysql> select Name,
row_number() over (partition by Name) as `Rank`
from DemoTable;

このクエリを実行すると、次のような結果が出力されます。

+--------+------+
| Name   | Rank |
+--------+------+
| Adam   | 1    |
| Adam   | 2    |
| Adam   | 3    |
| Adam   | 4    |
| Chris  | 1    |
| Chris  | 2    |
| Chris  | 3    |
| Robert | 1    |
| Robert | 2    |
+--------+------+
9 rows in set (0.00 sec)

このように、ROW_NUMBER()とPARTITION BYを組み合わせれば、各グループ内で1から始まる連番を簡単に生成できます。なお、ROW_NUMBER()はMySQL 8.0以降で使用可能なウィンドウ関数です。それ以前のバージョンをお使いの場合は、ユーザー変数（@変数）を活用した代替手法が必要になる点に注意してください。