SQLを教えてください

お世話になります。

商品マスタと商品カラーマスタと商品サイズマスタがあります。

商品マスタの一覧を出したいのですが、
商品に付随しているカラーやサイズも一緒に表示したいのです。

商品マスタ（商品ID・商品名）
商品カラーマスタ（商品カラーマスタID・商品ID・カラーID）
商品サイズマスタ（商品サイズID・商品ID・サイズID）
カラーマスタ（カラーID・カラー名）
サイズマスタ（サイズID・サイズ）
という構成です。
商品カラー、商品サイズは商品に対して複数あります。

表示したい一覧は
商品ID・商品名・カラー・サイズ
で
商品１つに対して１行で表し
カラー列、サイズ列は「赤・黒・茶」「S・M・L」というように
書きたいのです。

SQLでズバッと取得する方法はありますか？
１行１行、商品ごとに
カラー・サイズをループしなくてはいけないのでしょうか？

教えてください。
よろしくお願いします。

No.4 ベストアンサー 回答者： nora1962 回答日時： 2011/10/13 00:56

create view 商品サイズ as

select 商品ID, sys_connect_by_path(サイズ名,',') as サイズ名
from (select 商品マスタ.商品ID,商品マスタ.商品名,サイズマスタ.サイズ名,
Row_Number() over(partition by 商品マスタ.商品ID order by 商品サイズマスタ.サイズID) as Rank,
count(商品マスタ.商品ID) over(partition by 商品マスタ.商品ID) as MaxLevel
from 商品マスタ join 商品サイズマスタ
on 商品マスタ.商品ID = 商品サイズマスタ.商品ID
join サイズマスタ
on 商品サイズマスタ.サイズID = サイズマスタ.サイズID )
where Level = MaxLevel
start with Rank = 1
connect by Prior 商品ID = 商品ID
and Prior Rank = Rank-1;

create view 商品カラー as
select 商品ID,商品名, sys_connect_by_path(カラー名,',') as カラー名
from (select 商品マスタ.商品ID,商品マスタ.商品名,カラーマスタ.カラー名,
Row_Number() over(partition by 商品マスタ.商品ID order by 商品カラーマスタ.カラーID) as Rank,
count(商品マスタ.商品ID) over(partition by 商品マスタ.商品ID) as MaxLevel
from 商品マスタ join 商品カラーマスタ
on 商品マスタ.商品ID = 商品カラーマスタ.商品ID
join カラーマスタ
on 商品カラーマスタ.カラーID = カラーマスタ.カラーID )
where Level = MaxLevel
start with Rank = 1
connect by Prior 商品ID = 商品ID
and Prior Rank = Rank-1;

で
select 商品カラー.*,商品サイズ.サイズ名
from
商品カラー join 商品サイズ
on 商品カラー.商品ID = 商品サイズ.商品ID;

まあ、かなり無理やりですね。一旦VIEWにしないとOracleの構文解析のバグなのか一発で通らないし、階層別問合せの結果はINDEXが使えないので負荷が高いです。

この回答へのお礼

回答ありがとうございます。

うーん、難しそうですね。

もう少し勉強してから
試してみたいと思います。

お礼日時：2011/10/13 14:32

No.3 回答者： nda23 回答日時： 2011/10/12 22:41

手元にOracleが無いけど、こんな感じかな。

SELECT A.商品ID,A.商品名,
NVL2(C.カラーID,C.カラー名 || '・','') ||
NVL2(E.カラーID,E.カラー名 || '・','') ||
NVL2(G.カラーID,G.カラー名 || '・','') カラー,
NVL2(I.サイズID,I.サイズ || '・','') ||
NVL2(K.サイズID,K.サイズ || '・','') ||
NVL2(M.サイズID,M.サイズ || '・','') サイズ
(((((((((((FROM 商品マスタ A
LEFT OUTER JOIN 商品カラーマスタ B ON A.商品ID=B.商品ID)
LEFT OUTER JOIN
(SELECT カラーID,カラー名 FROM カラーマスタ WHERE カラー名='赤') C
ON B.カラーID=C.カラーID)
LEFT OUTER JOIN 商品カラーマスタ D ON A.商品ID=D.商品ID)
(SELECT カラーID,カラー名 FROM カラーマスタ WHERE カラー名='黒') E
ON D.カラーID=E.カラーID)
LEFT OUTER JOIN 商品カラーマスタ F ON A.商品ID=F.商品ID)
(SELECT カラーID,カラー名 FROM カラーマスタ WHERE カラー名='黒') G
ON F.カラーID=G.カラーID)
LEFT OUTER JOIN 商品サイズマスタ H ON A.商品ID=H.商品ID)
(SELECT サイズID,サイズ FROM サイズマスタ WHERE サイズ='S') I
ON H.サイズID=I.サイズID)
LEFT OUTER JOIN 商品サイズマスタ J ON A.商品ID=J.商品ID)
(SELECT サイズID,サイズ FROM サイズマスタ WHERE サイズ='M') K
ON J.サイズID=K.サイズID)
LEFT OUTER JOIN 商品サイズマスタ L ON A.商品ID=L.商品ID)
(SELECT サイズID,サイズ FROM サイズマスタ WHERE サイズ='L') M
ON L.サイズID=M.サイズID

古いバージョンだと外部結合の記述が違います。
理論は例えば「赤」ですが、赤だけ抽出したサブクエリを作り、
これに外部結合します。赤があれば結合しますが、無ければNULLです。
NVL2関数でNULLでない時と、NULLの時の編集をします。
同じことを他の色やサイズにも応用します。

イマイチ、やりたいことが分からないのですが、どういう表示に
したいかもう少し補足してください。

この回答へのお礼

回答ありがとうございます。

カラーもサイズも例として挙げただけで、
種類がもっとあるのです。

分かりにくい説明ですみません。

難しそうですね。

お礼日時：2011/10/13 14:32

No.2 回答者： yorozu_ya 回答日時： 2011/10/12 22:36

＞商品カラー、商品サイズは商品に対して複数あります。

最大いくつありますか？
上限を決められるなら可能です。

分析関数を使ってｎ番目の値を順に結合すればできます。

この回答へのお礼

回答ありがとうございます。

上限はわかりません。
難しそうですね。

お礼日時：2011/10/13 14:30

No.1 回答者： aenvgielle 回答日時： 2011/10/12 21:33

無理だと思います。

リレーショナルデータベースではカラムが複数の値をもつのは不可能なので。