はじめに
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での代替方法についても合わせて紹介した。