Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

はじめに

例えば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

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

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

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.(自分の)点数

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

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

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

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します。
「科目ごとに連番」とは、つまり「同じ科目の中で点数の高いレコードが何件あるか」とも言えるので、サブクエリ部分はこのようになります:arrow_down:

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

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

まとめ

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

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

おまけ

参考文献

webサイト

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

書籍

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

サンプルデータ

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

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);
mtanabe
画面系に抵抗感のないJavaエンジニア。最近はMySQLとも仲がいいです。 設計から実装、テスト、チームメンバーの成果物のレビューなどなど、幅広くやってます。
techfun
Tech FunはITの力で世界を豊かにする総合サービス企業です。 IT研修スクール「TechFun.jp(https://techfun.jp/)」、eラーニングプラットフォーム「StudySmile(https://studysmile.com/)」のほか、ミャンマーオフショア開発、スマートフォンアプリ開発、Webシステム開発、SIサービスを展開しています。
https://www.techfun.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away