解答と解説
予想外に皆さんすばやい解答を寄せていただいてありがとうございます。明智さん、カノエさん、Tergaviさん、お見事でした。それじゃ少し早いけど解答・解説するとしましょうか。
この問題のポイントは二つあります。まず一つ目
(1)行数に関してみると、クロス結合は掛け算として機能する
いま、片方のテーブルは行数が1であることが固定されています。だから、これをn行に増やしたいならもう一つn行持ったテーブルを用意して 1 * n = n の計算をしてやればいいのです。この「掛け算としての結合」は『指南書』のp.92で説明しています。
(2)連番テーブルの作り方
で、そうすると残る問題は、n行の連番テーブルを作ることです。いくつか方法はありますが、大きく以下の三つに要約できます。
- クロス結合でシーケンスビューを作る
実装非依存でどんなDBでも使えるメリットがあります。しかし、クロス結合は高コストなのと、当たり前ですが、連番の上限が自分が作ったテーブルの行数に制限されるデメリットもあります。『指南書』の「1-9 SQLで数列を扱う」で紹介している方法です。 - CONNECT BY句を使う。
非常にコードが簡潔に書けるのがメリット。テーブルも最初から用意されているdualテーブルを使えばいい。Tergaviさんの方法はこれです。WITH句を使わない点を除けば、私が採用したのもこの方法。ただし、Oracleのみでしか通用しないのが難点。 - 再帰共通表式を使う。
実装非依存でどんなDBでも使える。しかも連番に上限もないというすぐれもの（現実には実装が許す再帰の回数に依存するかもしれない）。ただし、再帰的に何度もクエリを発行するのでパフォーマンスは悪い。そして今のところ、現実的にDB2とSQLServerでしか使えないのも残念。でも将来的にはこの方法が一番有望な気はする。セルコも『Thinking in Sets』の「5.1.1 Creating Sequence Table」で紹介している。
解説はこんなところかな。最後に、説教爺として一言述べておくと、クロス結合する場合はテーブルをカンマで並べるよりも、なるべく「CROSS JOIN」構文を使いましょう。クロス結合はSQLの演算の中でも最も高コストであり、そのため、コードの中でこの演算を行っているということを目立つように記述することは、大事な意味を持ちます。カンマでテーブルを区切るだけだと、クロス結合かどうかはWHERE句を見ないと判別できない。よろしいかな。
それでは、家元は寝るぞよ。今週もよく働いた。皆さんもお疲れ様。
よい週末を。
