サブクエリー②(複雑なサブクエリー)



サブクエリー②(複雑なサブクエリー)

ここでは、サブクエリーをいろいろな技法と組み合わせることによって、全体としてより複雑なクエリーを作り上げる方法を見ていきます。

1.集計関数の利用

前回説明したように、結果が1つに定まらないサブクエリーはエラーになりますが、結果を1つに定めるために集計関数を使うことができます。集計関数のSUM、COUNT、MIN、MAX、AVGは、いずれも1つの結果を返します。次のSQLは、サブクエリーの中で受注数量の平均を求めて、その平均受注数量より
大きい受注合計の情報を抽出しています。

SELECT J.受注日, J.得意先名, J.数量 * S.単価 受注合計
FROM 受注 J,商品 S
WHERE J.商品コード = S.商品コード
AND J.数量 * S.単価 >
(SELECT AVG(J.数量 * S.単価) FROM 受注 J,商品 S WHERE J.商品コード = S.商品コード);

まず、サブクエリの結果を見てみます。
SELECT AVG(J.数量 * S.単価) FROM 受注 J,商品 S WHERE J.商品コード = S.商品コード

結果は以下のようになります。

なので、最初のSQLは、この平均受注合計より大きいデータが対象となります。
結果は以下のようになります。

WHERE句を外して全件抽出した際の結果

ということで、最初のSQLの結果が正しく平均受注合計より大きいデータを抽出していることが分かります。

2.サブクエリーのネスト

サブクエリーの中に別のサブクエリーを埋め込むことができます。このように、サブクエリーの中にサブクエリーを埋め込むことをサブクエリーのネストといいます。ここでは、サブクエリーのネストを利用して、平均受注額を超える受注のあった得意先に特別な通知を送付するためのクエリーを作成してみます。

商品表

得意先表

受注表

クエリー全体は以下のようになります。このクエリーでは、2つのサブクエリーをネストして使って、該当する得意先の情報を出力しています。

SELECT T.得意先名, T.住所, T.電話番号
FROM 得意先 T
WHERE T.得意先名 IN
(SELECT J.得意先名 FROM 受注 J, 商品 S WHERE J.商品コード = S.商品コード
AND
J.数量 * S.単価 >
(SELECT AVG(J.数量 * S.単価) FROM 受注 J,商品 S WHERE J.商品コード = S.商品コード)
);

結果は以下のようになります。

詳しく説明します。
①.2番目のネストSQL:SELECT AVG(J.数量 * S.単価) FROM 受注 J,商品 S WHERE J.商品コード = S.商品コード
この結果は以下のとおりです。

②.1番目のネストSQL:SELECT J.得意先名 FROM 受注 J, 商品 S WHERE J.商品コード = S.商品コード AND J.数量 * S.単価 > 3222.8571
下線部は①の結果になります。この結果は以下のとおりです。

③.全体のSQL:
SELECT T.得意先名, T.住所, T.電話番号 FROM 得意先 T
WHERE T.得意先名 IN (‘東京飯店’,’東京飯店’,’杉並食堂’,’新宿レストラン’,’杉並食堂’)
下線部は②の結果になります。この結果は以下のとおりです。

3.GROUP BY句やHAVING句の使用

サブクエリーは、WHERE句だけでなく、GROUP BY句を使うクエリーのHAVING句でも使うことができます。次の例を見てみましょう。

SELECT 得意先名, AVG(数量) FROM 受注
GROUP BY 得意先名
HAVING AVG(数量) > (SELECT AVG(数量) FROM 受注);

結果は以下のようになります。

このSQLも分解してみてみます。まず、HAVING以下の条件を外した結果を見てみます。

SELECT 得意先名, AVG(数量) FROM 受注 GROUP BY 得意先名;

結果は以下のようになります。

次にサブクエリの中のSQLを実行結果は以下のようになります。

SELECT AVG(数量) FROM 受注;

結果は以下のようになります。

ですので、最初のSQLを簡単に書くと以下のとおりとなります。

SELECT 得意先名, AVG(数量) FROM 受注
GROUP BY 得意先名
HAVING AVG(数量) > 10.2143;

結果は以下のようになります。

このように、一見すると複雑なSQLも分解して、一番深いSQLから一つづつ結果を求めていけば全体のSQLもスッキリします。今回の複雑なサブクエリについては以上となります。次回は相関サブクエリの説明をします。

 

 

 

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です