SQLの質問です。

Oracle Database Express Editionを使って勉強中の者です。
商品TBL、予約TBL、明細TBLと3つのテーブルを使用して、商品に対して予約と受取の明細がきちんと出ているのか、件数を出すSQLを組みたいと思っています。
例えば、

【商品TBL：shohin】
shohin_no / shohin_nm / price
-----------------------------
s001 / name1 / 1000
s002 / name2 / 1500
s003 / name3 / 2000

【予約TBL：yoyaku】
yoyaku_no / shohin_no / status(1:予約, 2:受取)
-----------------------------
y001 / s001 / 1
y001 / s001 / 2
y002 / s001 / 1
y002 / s002 / 1
y002 / s002 / 2

【明細TBL：receipt】
shohin_no / status / receipt
-----------------------------
s001 / 1 / abc..
s001 / 2 / def..
s002 / 1 / ghi..

【想定結果】
商品NO / 予約数 / 予約明細数 / 受取数 / 受取明細数
-----------------------------
s001 / 2 / 1 / 1 / 1
s002 / 1 / 1 / 1 / 0
s003 / 0 / 0 / 0 / 0

としたいのですが、どうにも上手くいかず、下記のような結果が返ってきます。

【結果】
商品NO / 予約数 / 予約明細数 / 受取数 / 受取明細数
-----------------------------
s001 / 2 / 2 / 2 / 2
s002 / 1 / 1 / 1 / 0
s003 / 0 / 0 / 0 / 0

私が実行したSQLは下記です。
恐らく何か初歩的な所で間違っているような気がするのですが、自分では分からず…。
一体何がいけないのか、ご教授の程、よろしくお願い致します。

select shohin.shohin_no as "商品NO"
,count(yoyaku_1.shohin_no) as "予約数"
,count(receipt_1.shohin_no) as "予約明細数"
,count(yoyaku_2.shohin_no) as "受取数"
,count(receipt_2.shohin_no) as "受取明細数"
from shohin
,(select * from yoyaku where yoyaku.status = '1') yoyaku_1
,(select * from yoyaku where yoyaku.status = '2') yoyaku_2
,receipt receipt_1
,receipt receipt_2
where yoyaku_1.shohin_no(+) = shohin.shohin_no
and yoyaku_2.shohin_no(+) = shohin.shohin_no
and receipt_1.shohin_no(+) = yoyaku_1.shohin_no
and receipt_1.status(+) = yoyaku_1.status
and receipt_2.shohin_no(+) = yoyaku_2.shohin_no
and receipt_2.status(+) = yoyaku_2.status
group by shohin.shohin_no
order by 1

No.1 ベストアンサー 回答者： Siegrune 回答日時： 2013/07/19 00:50

select a.a1,count(b.a1),count(c.a1) from a,b,c

where a.a1 = b.a1(+) and a.a1 = c.a1(+)
group by a1
でcount(b.a1)とcount(c.a1)の値がどのようにでてくるかの理解が違っています。
★a.a1
1
2
3
★b.a1,a2
1,101
1,102
2,201
★c.a1,a3
2,222
3,333
3,444
というデータになっていると、
select a.a1,b.a1,b.a2,c.a1,c.a3 from a,b,c
where a.a1 = b.a1(+) and a.a1 = c.a1(+)
は、
a.a1,b.a1,b.a2,c.a1,c.a3
1,1,101,,
1,1,102,,
2,2,201,2,222
3,,3,333
3,,3,444
となります。
で、
select a.a1,count(b.a1),count(c.a1) from a,b,c
where a.a1 = b.a1(+) and a.a1 = c.a1(+)
group by a1
は、
1,2,2
2,1,1
3,2,2
となります。
b.a1やc.a1がNullであろうが値が入っていようが、1件としてカウントされます。
select a.a1,sum(case b.a1 isnull then 1 else 0 end)
,sum(case c.a1 isnull then 1 else 0 end) from a,b,c
where a.a1 = b.a1(+) and a.a1 = c.a1(+)
group by a1
なんてしたら、
1,2,0
2,1,1
3,0,2
とでてきます。

方法１：がんばってこれでやってみる。
select shohin.shohin_no as "商品NO"
,sum(case when yoyaku_1.shohin_no is null then 0 else 1 end) as "予約数"
,sum(case when receipt_1.shohin_no is null then 0 else 1 end) as "予約明細数"
,sum(case when yoyaku_2.shohin_no is null then 0 else 1 end) as "受取数"
,sum(case when receipt_2.shohin_no is null then 0 else 1 end) as "受取明細数"
from shohin
,(select * from yoyaku where yoyaku.status = '1') yoyaku_1
,(select * from yoyaku where yoyaku.status = '2') yoyaku_2
,(select * from receipt where receipt.status = '1') receipt_1
,(select * from receipt where receipt.status = '2') receipt_2
where yoyaku_1.shohin_no(+) = shohin.shohin_no
and yoyaku_2.shohin_no(+) = shohin.shohin_no
and receipt_1.shohin_no(+) = yoyaku_1.shohin_no
and receipt_1.status(+) = yoyaku_1.status
and receipt_2.shohin_no(+) = yoyaku_2.shohin_no
and receipt_2.status(+) = yoyaku_2.status
group by shohin.shohin_no
order by 1
どう考えても処理遅そう。

方法２：group by した結果を結合する。
select shohin.shohin_no as "商品NO"
,nvl(yoyaku_1.cnt,0) as "予約数"
,nvl(receipt_1.cnt,0) as "予約明細数"
,nvl(yoyaku_2.cnt,0) as "受取数"
,nvl(receipt_2.cnt,0) as "受取明細数"
from shohin
,(select shohin_no,count(*) as cnt from yoyaku
where yoyaku.status = '1' group by shohin_no) yoyaku_1
,(select shohin_no,count(*) as cnt from yoyaku
where yoyaku.status = '2' group by shohin_no) yoyaku_2
,(select shohin_no,count(*) as cnt from receipt
where receipt.status = '1' group by shohin_no) receipt_1
,(select shohin_no,count(*) as cnt from receipt
where receipt.status = '2' group by shohin_no) receipt_2
where yoyaku_1.shohin_no(+) = shohin.shohin_no
and yoyaku_2.shohin_no(+) = shohin.shohin_no
and receipt_1.shohin_no(+) = shohin.shohin_no
and receipt_2.shohin_no(+) = shohin.shohin_no
group by shohin.shohin_no
order by 1
注：nvl(yoyaku_1.cnt,0)は、yoyaku_1.cntがNull(対象商品がない)時に0を表示します。 　

方法３：スカラー副問い合わせを使う。
select shohin.shohin_no as "商品NO",
(select count(*)
from yoyaku where yoyaku.status = '1'
and yoyaku.shohin_no = shohin.shohin_no
) as "予約数",
(select count(*)
from receipt where receipt.status = '1'
and receipt.shohin_no = shohin.shohin_no
) as "予約明細数",
(select count(*)
from yoyaku where yoyaku.status = '2'
and yoyaku.shohin_no = shohin.shohin_no
) as "受取数",
(select count(*)
from receipt where receipt.status = '1'
and receipt.shohin_no = shohin.shohin_no
) as "受取明細数"
from shohin
order by 1

※receiptはshohinと結合すべきでないのかもしれませんが、
　あってはいけないyoyakuと結合できないreceiptは
　（質問のようにyoyakuとreceiptを結合するとカウントされないが、
　　この回答ではカウントされてしまう。）
　質問に示された【想定結果】では調べようがないので、
　別途調べるselect文を実行するだろうから、手っ取り早く、
　shohinと結合してしまいました。

## 大量データを投入して、全部動かしてみてどれが早いか試してみるのも、
## 勉強のうちなのかも。

この回答へのお礼

こんな時間に素早いご回答、感謝します！
解説もとてもわかりやすかったです。
まず1回グループ化する前の全体像を把握すべきだったのですね。
また、改善案を3つも書いて頂き、本当にありがとうございます。
試してみたところ、しっかり想定通りの結果が出ました。
(方法1はうまくいきませんでしたが…これは自分で確かめてみます！)

大量データは、ちょっと今日はもう遅いので、明日明後日辺りに試してみたいと思います。
これで今日はぐっすり眠れそうです(笑)
本当にありがとうございました。

お礼日時：2013/07/19 02:10

No.2 回答者： yamada_g 回答日時： 2013/07/19 09:32

そもそも明細TBLに予約TBLとの関連がないのが良くないと思うのですが。

それがあればもっと単純なSQLでよくなるはずです。

この回答へのお礼

自分で勉強用にこんな感じかなぁと思って作成したものでしたので、未熟なのは分かっていましたが…なるほど、テーブルを見直すという選択肢もあるのですね。
今後の参考にさせて頂きます。
ご指南、ありがとうございました！

お礼日時：2013/07/20 10:04