sqlite3 で SELECT 結果の行番号を取得する
SQL を使っていると、SELECT した結果セットの各行について、結果セット中での行番号を知りたい、という場面にたまに遭遇します。こういうやつです。SELECT * FROM books ORDER BY price;
id title price
---------- -------------- ----------
3 Art with Basis 600 #行番号: 1
4 Book List 990 #行番号: 2
1 Civilizations 1000 #行番号: 3
2 Dream Post 1200 #行番号: 4
5 El sant magina 1500 #行番号: 5
一般的な一覧系の処理では先頭から N 件とりだすことが多いので、OFFSET で取り出した件数を指定すれば行番号を意識せずに続きをとることができます。
ただ、ID からその行の結果セット中での位置を知ろうとすると、やはり行番号が欲しくなってくるのです。結果セットがソートされていたり、ソートされているカラムがユニークでなかったりするとかなり面倒なことになります。特定の ID の行の前後 3 行ずつを取りたい、というような時に困ってしまうわけです。
結果セット中での行番号を取得するために、Oracle では ROWNUM 擬似列や ROW_NUMBER() 関数が用意されているそうです。
また、MySQL ではユーザ定義変数を使うことで、SELECT 時に行番号を計算し、出力することができます。SET @rownum := 0; SELECT *, (@rownum := @rownum + 1) FROM books;
しかし sqlite3 では ROW_NUMBER() 的な機能もユーザー定義変数的な機能も実装されていないので、これらの方針をとることはできません。ネットを見てみると以下のようなクエリで計算している例がありますが、境界値として WHERE 句で使っているカラム（ここでは id カラム）がユニークでない場合、境界値として機能しないため、厳密な行番号はとれません。SELECT id, title, price, (SELECT COUNT(*) FROM books b2 WHERE b1.id >= b2.id) AS rownum FROM books b1 ORDER BY rownum;
id title price rownum
---------- -------------- ---------- ----------
3 Art with Basis 600 1
4 Book List 990 2
1 Civilizations 1000 3
2 Dream Post 1200 4
5 El sant magina 1500 5
※ ORDER BY rownum は見やすさ重視のためにつけたもので、必須ではありません。
もう 1 点、この方法では 1 行毎にサブクエリで行番号をカウントするため、参照性能に問題があります。MySQL では悪名高き DEPENDENT SUBQUERY にあたるためです。
続きを読む
WEB 技術
コメント (0)
2017/02/01 19:45:13
