あるレコードを、そのレコードに含まれるカウント用カラムに設定された数だけ増殖させる SQL

投稿の内容を適切に表現したタイトル募集中！

改版履歴

2014/04/17 Oracle での連番作成のやり方を追記

やりたいこと

table name : table1

id	name	num
1	aaa	2
2	bbb	3
3	ccc	5
4	ddd	1

こんなテーブルを、num の数に着目し

id	name	seq
1	aaa	1
1	aaa	2
2	bbb	1
2	bbb	2
2	bbb	3
3	ccc	1
3	ccc	2
3	ccc	3
3	ccc	4
3	ccc	5
4	ddd	1

こんな感じで増幅させたいことって、よくはないけどたまにはありますよね？

やりかた (MySQL の場合)

select
  t1.id
  , t1.name
  , seq_t.seq
from
  table1 as t1
  , (select
       @i := @i + 1 as seq
     from
       information_schema.tables
       , (select @i := 0) as dummy
     where
       @i < 5) as seq_t
where
  t1.num >= seq_t.seq order by t1.id, seq_t.seq;

なんでそうなるの？

連番作成

select
  @i := @i + 1 as seq
from
  information_schema.tables
  , (select @i := 0) as dummy
where
  @i < 5;

この、サブクエリの部分の SQL の結果は

seq
1
2
3
4
5

こうなります。

解説

(select @i := 0) as dummy で開始値、を設定し、@i := @i + 1 で一行ごとにインクリメントさせて表示しているだけです。とはいえ、それだけ（select @i := @i + 1 as num from (select @i := 0) as d;）では一行しか拾ってこれませんので、適当なテーブルを join させて行数を稼ぎます。上記の例では information_schema.tables を使っていますが、必要な行数を持っていることが保証できるならどんなテーブルでもいいです。（慣例的には、必ず存在してある程度の行数が保証されるシステムテーブルを使うことが多いです。）
無条件で結合させると information_schema.tables の行数分の連番ができてしまいますので、where 句を付けて適当な長さで切っています。

増幅

サブクエリの部分が連番作成をまかなっていることがわかりました。

select
  t1.id
  , t1.name
  , seq_t.seq
from
  table1 as t1
  , (連番作成) as seq_t
where
  t1.num >= seq_t.seq order by t1.id, seq_t.seq;

要するに、こういうことです。

解説

ここまで来ればもう簡単ですね。

from
  table1 as t1
  , (連番作成) as seq_t

増幅させたいテーブルと、連番テーブルの直積をとって

id	name	num	seq
1	aaa	2	1
1	aaa	2	2
1	aaa	2	3
1	aaa	2	4
1	aaa	2	5
2	bbb	3	1
2	bbb	3	2
2	bbb	3	3
2	bbb	3	4
2	bbb	3	5
3	ccc	5	1
3	ccc	5	2
3	ccc	5	3
3	ccc	5	4
3	ccc	5	5
4	ddd	1	1
4	ddd	1	2
4	ddd	1	3
4	ddd	1	4
4	ddd	1	5

そこから

where
  t1.num >= seq_t.seq

連番が num を超えない範囲のレコードのみを抽出すれば、無事要件を満たしました。めでたしめでたし。

Oracle の場合

Oracle の場合の連番作成

select rownum seq from all_catalog where rownum <= 5;

Oracle だと rownum があるのでわかりやすいですね。
ここでも、対象テーブルは適切な件数があるものなら何でもいいですが、やはり必ず存在してある程度の行数が保証されるシステムテーブルということで、慣例的に all_catalog が用いられることが多いようです。

注意点

直積を取るため、うっかりすると件数が爆発する可能性がありますので注意してください。