【SQL】row_numberのないMySQLでユーザー変数を使わずに連番を振る

はじめに

例えばoracleではrow_number()みたいな分析関数が用意されているのですが、MySQLではrow_number()がないので、サブクエリを使って連番を振ってみようと思います。

サンプルデータ

適当に作った、テストの成績表を使います。

名前	科目	点数
sato	Math	65
morita	Science	85
tahakashi	Math	45
ishii	English	75
ishii	Math	40
tahakashi	Science	60
shimizu	English	55
nakajima	English	70

点数の高い順に並び替えて連番を振る

上記のサンプルデータを以下のように表示します。ひとまず科目は問いません。
学校だと、点数の高い順に掲示板に張り出されるアレです

No.	名前	科目	点数
1	morita	Science	85
2	ishii	English	75
3	nakajima	English	70
4	sato	Math	65
5	tahakashi	Science	60
6	shimizu	English	55
7	tahakashi	Math	45
8	ishii	Math	40

実行したSQL

select
  (select count(*)+1 from テストの成績表 where 点数 >x.点数) as 'No.'
  , 名前, 科目, 点数
from テストの成績表 as x
order by x.点数 desc
;

解説

「点数の高い順に」ということなので、order by x.点数 descで並び替えます。
その時に、「自分より点数の高いレコードが何件あるか」を算出しているのがこのサブクエリです。

select count(*)+1 from テストの成績表 where (比較対象の)点数 >x.(自分の)点数

１位のmorita / Science / 85さんより点数の高いレコードは0件です。
そのため、「自分より点数の高いレコードの件数」に+1しないと、１から始まる連番にならないというわけです。
ポイントは、「再帰集合になっている」こと
count(*)する要素数が必ず１つずつ増えていくのが保証されるんです。
ちなみに、点数の低い順に連番を振りたければ、where 点数 <x.点数と不等号の向きを逆にします。

点数の高い順に並び替えて科目ごとに連番を振る

上記の例だと使いづらいせめて科目ごとに順位つけるよね・・・ってことで、以下のように表示してみます。

No.	名前	科目	点数
1	ishii	English	75
2	nakajima	English	70
3	shimizu	English	55
1	sato	Math	65
2	tahakashi	Math	45
3	ishii	Math	40
1	morita	Science	85
2	tahakashi	Science	60

実行したSQL

select
  (select count(*)+1 from テストの成績表
   where 科目 = x.科目 and 点数 > x.点数
  ) as 'No.'
  , 名前, 科目, 点数
from テストの成績表 as x
order by x.科目, x.点数 desc
;

解説

科目と点数で並べ替えたいので、ひとまずorder by x.科目, x.点数 descします。
「科目ごとに連番」とは、つまり「同じ科目の中で点数の高いレコードが何件あるか」とも言えるので、サブクエリ部分はこのようになります

select count(*)+1 from テストの成績表
where
  (比較対象の)科目 = x.(自分の)科目
  and (比較対象の)点数 > x.(自分の)点数

やってることは、さっきと同じです。科目が一致しているかどうかを見ることで、連番をリセットさせることが出来るというカラクリです

まとめ

row_number()が使えなくても、連番を振ることは可能です。
ただ、テーブルのスキャンが２回になるので、row_number()があるなら、素直にrow_number()を使った方がパフォーマンスは上がります。

とはいえ、考え方の１つとして、知っておいてもいいのかなーと思います。

おまけ

参考文献

webサイト

SQLアタマアカデミー
第1回　連番の特性を利用してデータ操作をもっと自由に　SQLで連番を扱う
（2）行集合に連番を割り当てる
　http://gihyo.jp/dev/serial/01/sql_academy2/000102

書籍

SQL実践入門 ──高速でわかりやすいクエリの書き方 WEB+DB PRESS plus
2015年4月11日発売
ミック　著
http://gihyo.jp/book/2015/978-4-7741-7301-6

サンプルデータ

今回のサンプルデータを生成したcreate table文とinsert文はこちら

create table testScore(name varchar(30), subject varchar(100), score integer);
insert into testScore(name, subject, score) values("sato", "Math", 65);
insert into testScore(name, subject, score) values("morita", "Science", 85);
insert into testScore(name, subject, score) values("tahakashi", "Math", 45);
insert into testScore(name, subject, score) values("ishii", "English", 75);
insert into testScore(name, subject, score) values("ishii", "Math", 40);
insert into testScore(name, subject, score) values("tahakashi", "Science", 60);
insert into testScore(name, subject, score) values("shimizu", "English", 55);
insert into testScore(name, subject, score) values("nakajima", "English", 70);