Oracle SQL SELECT3 : 副問合せ
- もう少し複雑な副問い合わせについて
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
FROM句の副問合せ
インラインビューの副問合せは、私自身WHERE句の副問合せより良く用いる方法です。考え方がわかりやすい点、利用価値が高い感じがします。良くある例として売上データの日毎の集計を一つのSELECT文で取得するものを以下に示します。
SQL> SELECT D01.金額 AS 売上1日 , D02.金額 AS 売上2日 , D03.金額 AS 売上3日
2 FROM
3 (
4 SELECT SUM(TD.売上数量 * TM.売上単価) AS 金額
5 FROM TT_売上 TU , TT_売上明細 TD , TM_商品 TM
6 WHERE TU.売上番号 = TD.売上番号
7 AND TD.商品コード = TM.商品コード
8 AND TU.売上日 = TO_DATE('2002/11/01')
9 ) D01,
10 (
11 SELECT SUM(TD.売上数量 * TM.売上単価) AS 金額
12 FROM TT_売上 TU , TT_売上明細 TD , TM_商品 TM
13 WHERE TU.売上番号 = TD.売上番号
14 AND TD.商品コード = TM.商品コード
15 AND TU.売上日 = TO_DATE('2002/11/02')
16 ) D02,
17 (
18 SELECT SUM(TD.売上数量 * TM.売上単価) AS 金額
19 FROM TT_売上 TU , TT_売上明細 TD , TM_商品 TM
20 WHERE TU.売上番号 = TD.売上番号
21 AND TD.商品コード = TM.商品コード
22 AND TU.売上日 = TO_DATE('2002/11/03')
23 ) D03
24 ;
売上1日 売上2日 売上3日
--------- --------- ---------
384000 560000
この例では親のSELECT文のFROM句に３個の副問合せのSELECT文を含んでいます。それぞれの副問合せはほとんど同じ形式で、TT_売上の売上日の条件が11月1日～11月3日に変わっているだけです。
副問合せはTT_売上、TT_売上明細、TM_商品から売上数量と売上単価を掛けた金額を合計する集計SELECT文です。
（実際に日毎の売上金額を集計する場合は、グループ集計を行えば上の例より簡単に求めることは出来ますが、副問合せの例ということで少々手の混んだSELECT文になっています）
SQL> SELECT TU.売上日, SUM(TD.売上数量 * TM.売上単価) AS 金額 2 FROM TT_売上 TU , TT_売上明細 TD , TM_商品 TM 3 WHERE TU.売上番号 = TD.売上番号 4 AND TD.商品コード = TM.商品コード 5 GROUP BY TU.売上日; 売上日 金額 -------- --------- 02-11-01 384000 02-11-03 560000 02-11-04 570000 02-11-13 396000 02-11-15 1346000
また、売上日の条件を変えればいろいろな集計が得られます。例えば1年の各月毎の売上集計を行ったり、各年毎の売上集計を行えます。以下に簡単な例を示します。
SQL> SELECT M10.金額 AS 売上10月 , M11.金額 AS 売上11月 , M12.金額 AS 売上12月
2 FROM
3 (
4 SELECT SUM(TD.売上数量 * TM.売上単価) AS 金額
5 FROM TT_売上 TU , TT_売上明細 TD , TM_商品 TM
6 WHERE TU.売上番号 = TD.売上番号
7 AND TD.商品コード = TM.商品コード
8 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/10'
9 ) M10,
10 (
11 SELECT SUM(TD.売上数量 * TM.売上単価) AS 金額
12 FROM TT_売上 TU , TT_売上明細 TD , TM_商品 TM
13 WHERE TU.売上番号 = TD.売上番号
14 AND TD.商品コード = TM.商品コード
15 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/11'
16 ) M11,
17 (
18 SELECT SUM(TD.売上数量 * TM.売上単価) AS 金額
19 FROM TT_売上 TU , TT_売上明細 TD , TM_商品 TM
20 WHERE TU.売上番号 = TD.売上番号
21 AND TD.商品コード = TM.商品コード
22 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/12'
23 ) M12
24 ;
売上10月 売上11月 売上12月
--------- --------- ---------
3256000
FROM句の副問合せは、複雑なSELECT文を考える場合に要求されている条件を段階的に処理して簡単な条件の組み合わせにしていくことが出来ます。初めてこの副問合せを見た時には目から鱗で、こんなことが出来るんだと思ったものでした。
非常に便利な機能ですがあまりネストを深くしたり、インラインビューを多くすると自分で何をやっているのかわからなくなりますのでほどほどにしたほうが良いかと思います。
FROM句の副問合せその2
今回は、前回あった月毎の売上金額の集計のSQLを少し変化させて、商品毎の各月毎（11月と12月のみですが）の売上数量を集計する様なSQLについて以下に例を示します。以下のSQLは、FROM句の中にSELECT文が入った副問い合わせの形をしています。7～14行では売上明細から11月の売上で商品毎の売上数量の集計の一覧をT1と言う仮の名前でテーブルとして扱っています。また、17～23行では同様に12月の売上の集計を行い、T2の名前を付けています。11月の売上を主たるテーブルとして、12月の売上にリンク指定を行っています。
SQL> SELECT
2 T1.商品コード
3 ,TS.商品名
4 ,T1.売上数量 AS 売上数量11月
5 ,T2.売上数量 AS 売上数量12月
6 FROM
7 (
8 SELECT
9 TD.商品コード
10 ,SUM(TD.売上数量) AS 売上数量
11 FROM TT_売上 TU , TT_売上明細 TD
12 WHERE TU.売上番号 = TD.売上番号
13 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/11'
14 GROUP BY TD.商品コード
15 ) T1 ,
16 (
17 SELECT
18 TD.商品コード
19 ,SUM(TD.売上数量) AS 売上数量
20 FROM TT_売上 TU , TT_売上明細 TD
21 WHERE TU.売上番号 = TD.売上番号
22 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/12'
23 GROUP BY TD.商品コード
24 ) T2 ,
25 TM_商品 TS
26 WHERE T1.商品コード = T2.商品コード(+)
27 AND T1.商品コード = TS.商品コード(+)
28 ORDER BY T1.商品コード
29 ;
商品コード 商品名 売上数量11月 売上数量12月
---------- ---------------------------------------- ------------ ------------
1 PC-9001 2
2 PC-9002 1
3 PC-9003 3
4 NOTE-1010 1
5 NOTE-1020 2
6 NOTE-1030 1
7 PRT-3001 2
8 PRT-4001 4 3
9 CRT-1001 3 1
10 CRT-2001 2
11 HUB-A001 1
11行が選択されました。
実はこのSQLには問題点があります。11月には売上データが存在しなくて、12月に売上データが存在する場合には、SQLの結果として表示されなくなります。これを解消するには、1年を通した商品毎のSELECT文を主テーブルの様に考えて、それに各月毎のSELECT文をリンクする様にすれば問題なく全てのデータが表示されます。
以下にそのSQLを示します。9～14行目で1年間に売上が発生した商品コードの一覧を返すSELECT文をTYとし、17～23、26～32、35～41の各行でそれぞれ10月、11月、12月のデータを抽出するSELECT文をFROM句の副問い合わせとしています。TYを主テーブルとして考え、残りのテーブルをLEFT-JOINのリンクを行っています。
3か月分しか例を示していませんが、残り1月～9月までを加えれば年間の商品毎の各月の売上数が一覧できるSELECT文が完成すると思います。また、各副問い合わせの中で、WHERE句の売上日のAND条件の書き方はBETWEEN TU.売上日 TO_DATE('2002/10/01') AND TO_DATE('2002/10/31')の様なTU.売上日に対して計算しない方が実行時間は速いと思います。
SQL> SELECT
2 TY.商品コード
3 ,TS.商品名
4 ,T10.売上数量 AS 売上数量10月
5 ,T11.売上数量 AS 売上数量11月
6 ,T12.売上数量 AS 売上数量12月
7 FROM
8 (
9 SELECT
10 TD.商品コード
11 FROM TT_売上 TU , TT_売上明細 TD
12 WHERE TU.売上番号 = TD.売上番号
13 AND TO_CHAR(TU.売上日,'YYYY') = '2002'
14 GROUP BY TD.商品コード
15 ) TY ,
16 (
17 SELECT
18 TD.商品コード
19 ,SUM(TD.売上数量) AS 売上数量
20 FROM TT_売上 TU , TT_売上明細 TD
21 WHERE TU.売上番号 = TD.売上番号
22 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/10'
23 GROUP BY TD.商品コード
24 ) T10 ,
25 (
26 SELECT
27 TD.商品コード
28 ,SUM(TD.売上数量) AS 売上数量
29 FROM TT_売上 TU , TT_売上明細 TD
30 WHERE TU.売上番号 = TD.売上番号
31 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/11'
32 GROUP BY TD.商品コード
33 ) T11 ,
34 (
35 SELECT
36 TD.商品コード
37 ,SUM(TD.売上数量) AS 売上数量
38 FROM TT_売上 TU , TT_売上明細 TD
39 WHERE TU.売上番号 = TD.売上番号
40 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/12'
41 GROUP BY TD.商品コード
42 ) T12 ,
43 TM_商品 TS
44 WHERE TY.商品コード = TS.商品コード(+)
45 AND TY.商品コード = T10.商品コード(+)
46 AND TY.商品コード = T11.商品コード(+)
47 AND TY.商品コード = T12.商品コード(+)
48 ORDER BY TY.商品コード
49 ;
商品コード 商品名 売上数量10月 売上数量11月 売上数量12月
---------- ---------------------------------------- ------------ ------------ ------------
1 PC-9001 2
2 PC-9002 1
3 PC-9003 3
4 NOTE-1010 1
5 NOTE-1020 2
6 NOTE-1030 1
7 PRT-3001 2 2
8 PRT-4001 4 3
9 CRT-1001 1 3 1
10 CRT-2001 2
11 HUB-A001 1
11行が選択されました。
WHERE句の副問合せ
WHERE句にSELECT文の問合せを含める方法で、以下に例を示します。
SQL> SELECT * FROM TT_売上明細
2 WHERE 商品コード = (
3 SELECT 商品コード FROM TM_商品
4 WHERE 商品名 = 'PC-9001'
5 );
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
1 1 1 2
6 4 1 1
6 5 1 1 売上番号６で明細番号５のデータ
6 6 1 1
WHERE句の括弧内のSELECT文は商品名が'PC-9001'である商品コードを返します。この部分のみを実行すると以下の様になります。
SQL> SELECT 商品コード FROM TM_商品
2 WHERE 商品名 = 'PC-9001';
商品コード
----------
1
結果的に最初の副問合せのSELECT文は以下の様な形に簡単化されます。
SQL> SELECT * FROM TT_売上明細
2 WHERE 商品コード = 1;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
1 1 1 2
6 4 1 1
6 5 1 1 売上番号６で明細番号５のデータ
6 6 1 1
このWHERE句の条件で商品コードと"1"は”＝”で結合されていますが、これは商品コードの条件が1個のみしかないということです。このことを考えながら以下のSELECT文と実行結果を見てください。
SQL> SELECT * FROM TT_売上明細
2 WHERE 商品コード = (
3 SELECT 商品コード FROM TM_商品
4 WHERE 商品名 LIKE 'PC%'
5 );
SELECT 商品コード FROM TM_商品
*
エラー行: 3: エラーが発生しました。
ORA-01427: 単一行副問合せにより2つ以上の行が返されます。
WHERE句内のSELECT文のWHERE条件がLIKE演算子で記述されていて、この括弧内を実行すると結果は2行以上のデータが返されてしまいます。メインのWHERE句は＝演算子なので1個の値しか右側に持ってこれないので結果として例の様にエラーが返されます。
このエラーを解消する場合には＝演算子ではなくIN演算子を用いるとうまくいきます。IN演算子は引数として2個以上の値を持てるからです。
SQL> SELECT * FROM TT_売上明細
2 WHERE 商品コード IN (
3 SELECT 商品コード FROM TM_商品
4 WHERE 商品名 LIKE 'PC%'
5 );
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
1 1 1 2
2 1 2 1
3 1 3 3
6 4 1 1
6 5 1 1 売上番号６で明細番号５のデータ
6 6 1 1
6行が選択されました。
WHERE句の副問合せに関しては今回はこのくらいにして、次回はもう少し複雑なものを取り上げたいと思います。
SELECT句の副問合せ
SQL> SELECT
2 TU.売上番号
3 ,TD.明細番号
4 ,TU.売上日
5 ,(
6 SELECT 得意先名 FROM TM_得意先 TM
7 WHERE TM.得意先コード = TU.得意先コード
8 ) AS 得意先名
9 ,(
10 SELECT 担当者名 FROM TM_担当者 TM
11 WHERE TM.担当者コード = TU.担当者コード
12 ) AS 担当者名
13 ,(
14 SELECT 商品名 FROM TM_商品 TM
15 WHERE TM.商品コード = TD.商品コード
16 ) AS 商品名
17 ,TD.売上数量
18 FROM TT_売上 TU , TT_売上明細 TD
19 WHERE TU.売上番号 = TD.売上番号
20 ORDER BY
21 TU.売上番号
22 ,TD.明細番号;
売上番号 明細番号 売上日 得意先名 担当者名 商品名 売上数量
---------- ---------- -------- -------------------- ---------- --------------- ----------
1 1 02-11-01 （株）青木商事 斎藤 PC-9001 2
1 2 02-11-01 （株）青木商事 斎藤 NOTE-1010 1
2 1 02-11-03 山本商店 島田 PC-9002 1
2 2 02-11-03 山本商店 島田 NOTE-1020 2
2 3 02-11-03 山本商店 島田 HUB-A001 1
3 1 02-11-04 （株）ソフトプラザ 鈴木 PC-9003 3
4 1 02-11-13 ソフトランド 山田 NOTE-1030 1
4 2 02-11-13 ソフトランド 山田 PRT-3001 2
5 1 02-11-15 （株）福井商事 山下 PRT-4001 3
5 2 02-11-15 （株）福井商事 山下 CRT-1001 2
6 1 02-11-15 （株）青木商事 多田 PRT-4001 1
6 2 02-11-15 （株）青木商事 多田 CRT-1001 1
6 3 02-11-15 （株）青木商事 多田 CRT-2001 2
13行が選択されました。
SELECT句の中にサブクエリーを使用する場合、元となるテーブルのコードと サブクエリー内で参照するテーブルのコードとのリンクを指定します。 このサブクエリーは考え方としては、その部分でサブルーチン的に必要なカラムを取得するといった感じでしょうか。
サブクエリーを用いなくても以下の様なSQLであれば、上の結果と同じものが取得できます。
こちらのSQLの方が普通かもしれませんが、FROM句にあまりにもテーブルが多くなった場合は
SELECT句にサブクエリーを持っていくことも、SQLを見やすくする方法かもしれません。
SQL> SELECT 2 TU.売上番号 3 ,TD.明細番号 4 ,TU.売上日 5 ,MC.得意先名 6 ,MT.担当者名 7 ,MS.商品名 8 ,TD.売上数量 9 FROM TT_売上 TU , TT_売上明細 TD , TM_得意先 MC , TM_担当者 MT , TM_商品 MS 10 WHERE TU.売上番号 = TD.売上番号 11 AND TU.得意先コード = MC.得意先コード(+) 12 AND TU.担当者コード = MT.担当者コード(+) 13 AND TD.商品コード = MS.商品コード(+) 14 ORDER BY 15 TU.売上番号 16 ,TD.明細番号;
表示上のカラム数を設定する場合は、以下のSQL-Plusのコマンドを使用します。
COLUMN 「カラム名」 FORMAT AXXX
「XXX」の部分はカラム数を指定します。 尚、この設定を解除する場合は、以下のコマンドを実行します。
COLUMN 「カラム名」 CLEAR
上記のSQLの実行する前に以下のコマンドを実行してあります。
COLUMN 得意先名 FORMAT A20 COLUMN 担当者名 FORMAT A10 COLUMN 商品名 FORMAT A15
これらのカラム数指定を解除するのは以下のコマンドです。
COLUMN 得意先名 CLEAR COLUMN 担当者名 CLEAR COLUMN 商品名 CLEAR
尚、1ページの表示行数を大きくすれば、タイトルが何度も表示されません。
SET PAGES 1000
