はじめに
例えば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);