ここから本文です

至急! お願いします! 助けてください!

tie********さん

2019/6/2611:03:10

至急!
お願いします!
助けてください!

SQLの相関福問い合わせの理解が難しすぎます
以下の
ようなテーブルをつくりました



CREATE TABLE Shohin
(shohin_id CHAR(4) NOT NULL,
shohin_mei VARCHAR(100) NOT NULL,
shohin_bunrui VARCHAR(32) NOT NUL

L,
hanbai_tanka INTEGER ,
shiire_tanka INTEGER ,
torokubi DATE ,
PRIMARY KEY (shohin_id));


インサートは以下のsqlです
BEGIN TRANSACTION;
INSERT INTO Shohin VALUES ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Shohin VALUES ('0002', '穴あけパンチ', '事務用品', 500, 320, '2009-09-11');
INSERT INTO Shohin VALUES ('0003', 'カッターシャツ', '衣服', 4000, 2800, NULL);
INSERT INTO Shohin VALUES ('0004', '包丁', 'キッチン用品', 3000, 2800, '2009-09-20');
INSERT INTO Shohin VALUES ('0005', '圧力鍋', 'キッチン用品', 6800, 5000, '2009-01-15');
INSERT INTO Shohin VALUES ('0006', 'フォーク', 'キッチン用品', 500, NULL, '2009-09-20');
INSERT INTO Shohin VALUES ('0007', 'おろしがね', 'キッチン用品', 880, 790, '2008-04-28');
INSERT INTO Shohin VALUES ('0008', 'ボールペン', '事務用品', 100, NULL, '2009-11-11');
COMMIT;




そこで以下のような
問い合わせをしました

商品分類ごとに平均単価を調べてみました

select shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,
(select avg(hanbai_tanka)
from shohin sub2
where sub1.shohin_id = sub2.shohin_id
group by shohin_bunrui)
from shohin sub1


すると商品単価と相関サブクエリによって呼び出された値が同じでした
私がネットで調べたところ相関サブクエリや相関副問い合わせというのは副問合わせの中に主問い合わせのデータベースを指定することで主問い合わせのデータベースのデータを一行づつ副問い合わせに投げていくという処理であると書いてあったので上記の副問い合わせであれば商品idを調べその副問い合わせの中で得られた分類ごとの販売単価の平均をもってきているのでいいと思ったんですけど


sqlの順序って
from→where →group by という順番なはずですよね
サブクエリを見ると
idで条件を限定して
分類でグループごとに分けて
そのグループごとの平均値を調べているのでは?


正しくは

select shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,
(select avg(hanbai_tanka)
from shohin sub2
where sub1.shohin_bunrui = sub2.shohin_bunrui
group by shohin_bunrui)
from shohin sub1


みたいです
これだと

group byを外した
以下の問い合わせと結果が同じになるのですが


select shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,
(select avg(hanbai_tanka)
from shohin sub2
where sub1.shohin_bunrui = sub2.shohin_bunrui
)
from shohin sub1


私の相関サブクエリに対する処理の認識が間違っているのでしょうか
正しい処理のプロセスを教えてください




失礼します
さらに追加で

select shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,
(select avg(hanbai_tanka)
from shohin sub2
group by sub1.shohin_mei = sub2.shohin_mei
)

from shohin sub1


は副問い合わせが複数行返しているとエラーがでましたが
データベースの中に同じ商品名のデータはないのにエラーが発生する理由がわかりません

閲覧数:
16
回答数:
1

違反報告

ベストアンサーに選ばれた回答

プロフィール画像

カテゴリマスター

nora1962jpさん

2019/6/2612:14:07

select shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,
(select avg(hanbai_tanka)
from shohin sub2
where sub1.shohin_id = sub2.shohin_id
group by shohin_bunrui)
from shohin sub1

主問合せの1行ごとに副問合せ(スカラサブクエリ)が評価されます。

where sub1.shohin_id = sub2.shohin_id

で副問合せでの抽出対象は1行に絞られます。
avg使っても分類ごとの平均にはなりません。

> sqlの順序って
> from→where →group by という順番なはずですよね
> サブクエリを見ると
> idで条件を限定して
> 分類でグループごとに分けて
> そのグループごとの平均値を調べているのでは?

サブクエリの評価順序も
from→where →group by

です。

select shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,
(select avg(hanbai_tanka)
from shohin sub2
where sub1.shohin_bunrui = sub2.shohin_bunrui
group by shohin_bunrui)
from shohin sub1

select shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,
(select avg(hanbai_tanka)
from shohin sub2
where sub1.shohin_bunrui = sub2.shohin_bunrui
)
from shohin sub1

where句で分類が同じものの平均を取得すればgroup by指定不要です。

select shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,
(select avg(hanbai_tanka)
from shohin sub2
group by sub1.shohin_mei = sub2.shohin_mei
)
from shohin sub1

ですが
group by sub1.shohin_mei = sub2.shohin_mei

は比較結果ごとの平均なのでtrueとfalse,unknownごとの平均になるので最大3行になります。
スカラサブクエリは複数行返せないのでエラーになります。

where sub1.shohin_mei = sub2.shohin_mei

にしたかったのでしょうか。

質問した人からのコメント

2019/6/26 16:02:55

わかりやすい説明ありがとうございました

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる