MySQLで問い合わせの結果に行番号を付与するには

はじめに

SQLを使っていると、問い合わせの結果に行番号を付与したい場合がある。

OracleなどのDBMSには、行番号を取得するための関数があらかじめ用意されているのだが、昨今の主流DBMSとなったMySQLにはそのような仕組みはない。
そこで、代わりとなる手段がないものか検討してみた。

結論から言うと、MySQL 8.0以降ではウィンドウ関数という一連の集計関数が使えるため、この関数を利用すればクエリーの結果に行番号を付与することが可能だ。

なお、MariaDBでもウィンドウ関数は利用可能となっている。
本記事ではMariaDB 10.4にて動作を確認している。

MySQL 5.xではウィンドウ関数は使えないので、変数を使うなどして工夫をする必要がある。

なお、この変数を使う方法については、ビューの内部では使用できない等の制限がある。
それに、近いうちに淘汰される方法だとも思われるため、最後に簡単に触れるのみとさせていただく。

結果に行番号を付与する

行番号を取得する関数は、ROW_NUMBER()だ。
文法は初見だとかなり奇妙に感じられるのだが、以下のようになっている。

ROW_NUMBER() OVER (ORDER BY 列名)

OVERというキーワードがあったり、途中にORDER BY句が入っていたりするが、利用者が指定する箇所は「列名」の部分だけだ。
ここに任意の列名を指定すると、検索結果をその列でソートし際の行番号が取得できる。

従って、この一連の文をSELECT文の列名の並びに指定すれば、クエリー結果に行番号を付与することができる。

サンプル

具体的なサンプルを用意して使用例を示そう。
以下のような表「tBalance」があったとする。

この表をamount順に表示し、結果の各行には行番号を付与したいものとする。
その場合、以下のようなSQLを発行すればよい。

SELECT ROW_NUMBER() OVER (ORDER BY amount) AS line, account, subAccount, amount FROM tBalance

すると、結果は以下のようになる。
データがamount順にソートされ、各行には行番号が付与されている。

思惑通りの結果が得られたことが分かるだろう。

MySQL 5.xの場合

MySQL 5.xではウィンドウ関数が使用できないため、変数を使って行番号を付与する必要がある。
前述した通り、この方法はビュー内では使用できない制限がある。

上記のサンプルと同等の機能を実現するSQLは以下となる。

SET @i = 0; SELECT @i := @i + 1 AS line, account, subAccount, amount FROM tBalance ORDER BY amount;

細かい説明は省略するが、変数の場合は初期化が必要となるためSQLをセミコロンで区切って2つ並べている。
変数の制約のため、このSQLをビュー内に記述することはできない。

ROW_NUMBER()を使うやり方と比べると制限があるのと、かつ直感的でもないため、MySQL 8.0 以降での使用はおすすめしない。

まとめ

MySQL 8.0で実装されたウィンドウ関数を利用して、問い合わせ結果に行番号を付与する方法を紹介した。
ウィンドウ関数が実装されていないMySQL 5.xでの代替方法についても合わせて紹介した。

コメントする

メールアドレスが公開されることはありません。