組み合わせの検索方法

質問

数字の組み合わせ毎に情報を管理しようと思うのですが、
以下のようなテーブル構成を考えました。

CREATE TABLE COMBINATIONS (
COMBINATION_ID NUMBER,
INFO VARCHAR2(100)
);

CREATE TABLE ELEMENTS (
COMBINATION_ID NUMBER,
ELEMENT_NUMBER NUMBER
);

例としては：

<COMBINATIONS>
COMBINATION_ID INFO
-------------- ----
1 ABC
2 DEF
3 GHI

<ELEMENTS>
COMBINATION_ID ELEMENT_NUMBER
-------------- --------------
1 1
1 2
2 100
2 200
3 100
3 200
3 300

です。
1,2の組合せの場合→ABC
100,200の組合せの場合→DEF
100,200,300の組合せの場合→GHI
のような具合です。

このように、数字の組合せを条件にINFOを
取得するには、どのようなSQL文がよいでしょうか。
データベースはOracleです。

回答No.4

taka_tetsu · ベストアンサー

>２．仮想テーブルのCOMBINATION_IDで<ELEMENTS>を検索し、
>組み合わせ全てを持ち、組み合わせ以外を持たない
>COMBINATION_IDで第２仮想テーブルを構成する。

確かにできますけど・・・
数が不定だと、ここのSQLをどうするかなんですよね。

ここの抽出方法が確定するんでしたらインラインクエリー使わなくても普通にSQLだけで抽出できます。

たとえば、100,200の2つの場合、

select INFO from COMBINATIONS, ELEMENTS
where COMBINATIONS.COMBINATION_ID = ELEMENTS.COMBINATION_ID
group by INFO
having sum(decode(ELEMENT_NUMBER, 100, 1, 200, 1, 0)) = 2;

とかすればできますよ。
＃decodeのところがねぇ・・・

お礼

とりあえずCOMBINATION_IDがわかればよいので、
回答の内容を単純化すると
SELECT COMBINATION_ID
FROM ELEMENTS
GROUP BY COMBINATION_ID
HAVING
SUM(DECODE(ELEMENT_NUMBER,100,1,200,1,0)) = 2;
となりましたが、これではCOMBINATION_ID=3のケースも
検索結果に含まれてしまうのですが、HAVING句を
SUM(DECODE(ELEMENT_NUMBER,100,1,200,1,0)) = COUNT(*)
として、正しい結果が得られるようになりました！

考え方は、100と200の組合せを検索したい場合、
DECODEを使用して100->1,200->1,その他->0として
一致件数をSUMして、それらが組合せ内の要素数
(COUNT)と一致する、という条件で検索する、という
方法です。前提として組合せ内に同じ要素が存在しない、
というのが必要なので、ELEMENTS表に一意キー
(COMBINATION_ID,ELEMENT_NUMBER)を作成する必要が
あります。

ありがとうございます。

回答No.3

binko1

仮想テーブルを経由すれば、１ＳＱＬでも可能な気がします。
１．目的のELEMENT_NUMBERを持つCOMBINATION_IDをユニークに取り出し仮想テーブルとする。
２．仮想テーブルのCOMBINATION_IDで<ELEMENTS>を検索し、組み合わせ全てを持ち、組み合わせ以外を持たないCOMBINATION_IDで第２仮想テーブルを構成する。
３．第２仮想テーブルと<COMBINATIONS>を結合する。
（結果は単一ではない）
文法は面倒なので省略。
他にも方法はありそうですが、実際に使用することが目的なら、table自体を変えたほうがよいように思えます。

お礼

2の部分でつまづきました。
「組み合わせ全てを持ち、組み合わせ以外を持たない」
の条件をWHERE句で書けませんでした。
No.4の回答にこの部分の補足がありそうなので、
よく見てみます。
ありがとうございました。

回答No.2

R-Fuji

私も無理だと思いますね。
以前にリレーショナルとして繋がっていないんではないでしょうか？

組み合わせが有限であれば、それを定義するテーブルを作りそれとリレーションすれば、”無い”部分をnullか何かで埋められると思いますが……。組み合わせが順不同であれば、これもえらいことになりますね（^_^;

お礼

やっぱり難しいですかね。
組合せ要素数が有限であれば、COMBINATIONSの列に
ELEMENT_NUMBER1,ELEMENT_NUMBER2...のように
もって、INSERT時に必ず昇順で格納すればいいかとは
思ったのですが。ありがとうございます。

回答No.1

taka_tetsu

普通のSQLでは無理でしょう(^^;;;;

組み合わせの数の個数が不定なんで。

それに、
>100,200の組合せの場合→DEF
>100,200,300の組合せの場合→GHI

となると、100、200を選択したときにはそれ以外の
値を持つ結果(今回は300を持つGHI)を排除しないといけないわけですし。

＃もちろん逆はできますよ。DEFから100,200を求めるのは。

お礼

やっぱり難しいですかね。ありがとうございます。