具体的なSQLの質問。私にとって難しい…

次のようなSQLが知りたいです。細かいですがお願いします。
「使用テーブル」　Orderテーブル、Order明細テーブル、価格テーブル
「各表の列」　Orderテーブル　[OrderNo]　売り先
　　　　　　　Order明細テーブル　[OrderNo] [商品名]　個数
　　　　　　　価格テーブル　［商品名］[売り先] 価格
[　]が主キーです。
つまり価格は売り先によって値段が違います。

３つのテーブルを結合してSELECTしOrderNo,商品名,価格を出したいです。
条件は各OrderNoについて個数が一番多い商品名とその価格を表示。
もし個数が同じなら、さらに商品名の文字列順序の早い方で１つ選択。
OrderテーブルにはOrderNoがあるが明細がない場合は、OrderNo以外ブランク。
価格テーブルに商品の価格が登録されてなかったら、価格部分はブランク。
次がややこしいです。
価格テーブルである商品について、すべての売り先に対して
同じ価格なら売り先に'*****’が入ります。
つまり、価格テーブルで１つの商品について個別の売り先で値段が
設定されてなくても、'*****'ならその商品の価格がわかることになります。
'*****'も個別の売り先も登録されていなかったら、その商品の値段がわかりません。
もし、価格テーブルに同じ商品について売り先が
'*****'と個別の売り先名の両方で登録されていたら個別の方の価格を選択。

以上です。
Orderテーブルに100件あれば、上のようにすれば100件でると思います。
よろしくお願いします。

No.5 ベストアンサー 回答者： k_o_r_o_c_h_a_n 回答日時： 2007/11/04 02:46

分析関数が使える環境なら・・

--価格管理が矛盾しないなら
select A.OrderNo,B.商品名,C.価格
from Order A
left join (select X.*,row_number() over(partition by OrderNo order by 個数 desc,商品名) R from Order明細 X) B
ON (A.OrderNo=B.OrderNo and R=1)
left join 価格 C ON((C.商品名,C.売り先) in ((B.商品名,A.売り先),(B.商品名,'*****')))

--価格管理が矛盾するなら
select A.OrderNo,B.商品名,nvl(D.価格,C.価格) as 価格
from Order A
left join (select X.*,row_number() over(partition by OrderNo order by 個数 desc,商品名) R from Order明細 X) B
ON (A.OrderNo=B.OrderNo and R=1)
left join 価格 C ON(C.商品名=B.商品名 and C.売り先='*****')
left join 価格 D ON(D.商品名=B.商品名 and D.売り先=A.売り先)

という感じで書けると思いますよ。（未検証、目視確認のみのＳＱＬです）

No.4 回答者： venzou 回答日時： 2007/11/03 16:10

＞このSQLの場合、もし価格テーブルに求める商品名がなかったらどうなるのでしょうか？

＞ブランクになると思うんですが自信がなくて。

ビュー：集計結果でLEFT JOINで結合しているので、
ブランク（NULL）になります。
（Accessで動作確認しました）


＞'*****'と個別価格の両方の行が絶対ないと仮定した上だとどうなるのでしょうか？
＞もっと簡単になりますか？

残念ながら、簡単にはなりませんね。

共通の価格は、商品名で決まります。
個別の価格は、商品名と売り先で決まります。

関数従属も異なりますし、意味的にも異なるデータです。

異なるデータなので、両方のデータが同時に存在しても、
不都合はありません。

テーブル設計上、それを同じフィールドに入れてる点が問題ですが、
別テーブルに分けても、複雑さはあまり変わりません。
（ビューが２つ減るだけです）

そもそもの内容（要求）が複雑なので、
ある程度複雑なSQLになるのは仕方ないと思いますよ。（＾＾；

No.3 回答者： venzou 回答日時： 2007/11/03 14:42

No1回答者です。

とりあえず一番ややこしい所のみ回答しましたが、
その他の部分も結構ややこしいですね。（＾＾；

順にビューを作っていくのが簡単だと思います。

＞条件は各OrderNoについて個数が一番多い商品名とその価格を表示。

各OrderNoの最大個数を調べる
ビュー：最大個数--------------------------
SELECT Order明細テーブル.OrderNO, Max(Order明細テーブル.個数) AS 個数
FROM Order明細テーブル
GROUP BY Order明細テーブル.OrderNO;
------------------------------------------

＞もし個数が同じなら、さらに商品名の文字列順序の早い方で１つ選択。

最大個数が重複した場合の商品名の処理
ビュー：最大明細---------------------
SELECT Order明細テーブル.OrderNO, Min(Order明細テーブル.商品名) AS 商品名, Order明細テーブル.個数
FROM Order明細テーブル INNER JOIN 最大個数 ON (Order明細テーブル.OrderNO = 最大個数.OrderNO) AND (Order明細テーブル.個数 = 最大個数.個数)
GROUP BY Order明細テーブル.OrderNO, Order明細テーブル.個数;
------------------------------------------

＞OrderテーブルにはOrderNoがあるが明細がない場合は、OrderNo以外ブランク。

LEFT JOIN でブランクの処理
ビュー：Order最大一覧---------------------
SELECT Orderテーブル.OrderNo, Orderテーブル.売り先, 最大明細.商品名, 最大明細.個数
FROM Orderテーブル LEFT JOIN 最大明細 ON Orderテーブル.OrderNo = 最大明細.OrderNO;
------------------------------------------

＞価格テーブルに商品の価格が登録されてなかったら、価格部分はブランク。

共通の価格のみ取り出す
ビュー：標準価格テーブル------------------
SELECT 価格テーブル.商品名, 価格テーブル.価格 AS 標準価格
FROM 価格テーブル
WHERE 価格テーブル.売り先="****";
------------------------------------------

個別の価格のみ取り出す
ビュー：個別価格テーブル------------------
SELECT 価格テーブル.商品名, 価格テーブル.売り先, 価格テーブル.価格 AS 個別価格
FROM 価格テーブル
WHERE 価格テーブル.売り先<>"****";
------------------------------------------

最終的な出力
ビュー：集計結果--------------------------
SELECT Order最大一覧.OrderNo, Order最大一覧.売り先, Order最大一覧.商品名, Order最大一覧.個数, 個別価格テーブル.個別価格, 標準価格テーブル.標準価格,
(CASE WHEN 個別価格 is Null THEN 標準価格 ELSE 個別価格 END) As 価格
FROM (Order最大一覧 LEFT JOIN 個別価格テーブル ON (Order最大一覧.売り先 = 個別価格テーブル.売り先) AND (Order最大一覧.商品名 = 個別価格テーブル.商品名)) LEFT JOIN 標準価格テーブル ON Order最大一覧.商品名 = 標準価格テーブル.商品名;
------------------------------------------


ビューの数はもう少し減らせるかもしれませんが、
全部を１つのSQLで表現するのは無理かな。（＾＾；

なお、私はAccessで動作確認しながら作ったので、
構文の違いなどあれば、読み替えてください。
（分かる範囲でOracle用に書き直してあります）

この回答への補足

返答ありがとうございます。とても参考になりました。
このSQLの場合、もし価格テーブルに求める商品名がなかったらどうなるのでしょうか？
ブランクになると思うんですが自信がなくて。

あと、ややこしい部分となってるところで、価格テーブルには同じ商品について、
'*****'と個別価格の両方の行が絶対ないと仮定した上だとどうなるのでしょうか？
もっと簡単になりますか？

補足日時：2007/11/03 14:56

No.2 回答者： SUPER-NEO 回答日時： 2007/11/03 14:30

こんにちは。

私も #1 さんのおっしゃるように、設計に問題があると思います。

Orderテーブルと、Order明細テーブルは、
[OrderNo]で結合することができますよね？
そして、Order明細テーブルと価格テーブルは、
[商品名]でしか結合できません。

つまり、ＥＲ図で設計を示した場合に、データの関係図が
なっていない、と思うのですが…。
そもそも[売り先]の存在が浮いていませんか？

No.1 回答者： venzou 回答日時： 2007/11/03 12:23

Oracleには詳しくないので、一般的なSQLの話です。

＞次がややこしいです。

ややこしい原因は、価格テーブルの設計にあります。
これはテーブルを分けておく方が良いと思います。
（実際テーブルを分けずにビューを作っても良いです）

標準価格テーブル　[商品名]、標準価格
個別価格テーブル　[商品名]、[売り先]、個別価格

こうして置けば、価格を決定するのは簡単です。

(CASE WHEN 個別価格 is Null THEN 標準価格 ELSE 個別価格 END) As 価格

#Oracleには詳しくないので、構文が間違っているかもしれませんが、
#意味は伝わるかな。（＾＾；