SQL 複数条件を設定

質問

使用言語はjavaで処理しており、
SQL Server2005にて下記のようなテーブル(T_Kekka)があります。

　　　ID　　　a-1　　　a-2　　　b-1　　　b-2　　　c-1　　　c-2　　　
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
　　　１　　　bbb　　　aaa　　　ddd　　　　ccc　　　eee　　　fff　　　
　　　２　　　ggg　　　bbb　　　ccc　　　　 hhh　　　iii　　　　jjj　　　
　　　３　　　bbb　　　mmm　　　ddd　　　ccc　　　fff　　　　eee　　　
　　　４　　　bbb　　　aaa　　　ccc　　　　ddd　　　qqq　　　rrr　　　
　　　５　　　zzz　　　sss　　　ttt　　　　　ccc　　　vvv　　　xxx　　　
　　　６　　　zzz　　　aaa　　　ttt　　　　　ccc　　　vvv　　　fff　　　
　　　７　　　zzz　　　zzz　　　zzz　　　　　zzz　　　zzz　　　　zzz　　　
　　　８　　　qqq　　　rrr　　　ccc　　　　　ddd　　　fff　　　　eee　　　

検索する値は下記の通りです。

検索値
｜a-1｜a-2｜b-1｜b-2｜c-1｜c-2｜
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
｜aaa｜bbb｜ccc｜ddd｜eee｜fff｜

期待値としては、aはaのグループで検索、bはbのグループで検索、cはcのグループで検索をし、
一致個数の多い順に出力したいと考えております。（理想は下記のような感じです。）
また、グループ内であれば逆転していてもＯＫです。
（例えばＩＤ：１のaグループのような感じです。）

*1　一致数が同じ場合はcグループの数が多いデータが最優先となり、その他はＩＤ順になります。

期待値
　　　ID　　　a-1　　　a-2　　　b-1　　　b-2　　　c-1　　　c-2　　　一致数
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
　　　１　　　bbb　　　aaa　　　ddd　　　ccc　　　eee　　　fff　　　　　6
　　　３　　　bbb　　　mmm　　　ddd　　 ccc　　　fff　　　 eee　　　　5
　　　８　　　qqq　　　rrr　　　 ccc　　　ddd　　　fff　　　　eee　　　　4・・・*1
　　　４　　　bbb　　　aaa　　　ccc　　　ddd　　　qqq　　　rrr　　　　 4
　　　６　　　zzz　　　aaa　　　ttt　　　 ccc　　　vvv　　　 fff　　　　　3
　　　２　　　ggg　　　bbb　　　ccc　　　hhh　　　iii　　　　 jjj　　　　　 2
　　　５　　　zzz　　　sss　　　ttt　　　 ccc　　　vvv　　　 xxx　　　　　1
　　　７　　　zzz　　　zzz　　　zzz　　　 zzz　　　zzz　　　 zzz　　　　　0

知識不足のため私には複雑すぎて悩んでいる状況なので、
どなた様か知識がある方にご教授願いたく思います。

また、まだテスト段階なので現状のようなテーブル構造にしてしまいましたが、
他のテーブル構造の方がデータが抽出しやすかったり、レスポンスがいい場合がありましたら、
ご教授いただけると助かります。

説明が分かりづらく表が見づらくて申し訳ございませんが、
宜しくお願い致します。

回答No.1

jamshid6 · ベストアンサー

うひゃー、まるでパズルのようですね。
検索条件は必ずワンセットだけですか、とか、細かいデータの入り方が不明ですが、今回の例なら以下のようにできます。
最初のワークテーブルに入れているところから、より検索に向いたテーブル構造が何かも察して頂けるのではと期待します。
(検索対象がresulttblに、検索条件はsrchtblに１行だけとしてみてください）

DECLARE @res TABLE　([ID][int],[CAT][varchar](1),[VAL][varchar](3))
DECLARE @srch TABLE　([CAT][varchar](1),[VAL][varchar](3))

INSERT INTO @res
SELECT ID,'a' CAT,a1 VAL FROM resulttbl
UNION ALL
SELECT ID,'a',a2 from resulttbl
UNION ALL
SELECT ID,'b',b1 from resulttbl
UNION ALL
SELECT ID,'b',b2 from resulttbl
UNION ALL
SELECT ID,'c',c1 from resulttbl
UNION ALL
SELECT ID,'c',c2 from resulttbl

INSERT INTO @srch
SELECT
CAT,VAL
FROM
(SELECT 'a' CAT,a1 VAL from srchtbl
UNION ALL
SELECT 'a',a2 from srchtbl
UNION ALL
SELECT 'b',b1 from srchtbl
UNION ALL
SELECT 'b',b2 from srchtbl
UNION ALL
SELECT 'c',c1 from srchtbl
UNION ALL
SELECT 'c',c2 from srchtbl) tmp
GROUP BY CAT,VAL

SELECT r.*,ISNULL(c.CNT,0)
FROM resulttbl r
LEFT OUTER JOIN
(SELECT wt.ID,
COUNT(*) CNT,
SUM(CASE WHEN wt.CAT='c' THEN 1 ELSE 0 END) CNT_C
FROM @res wt
INNER JOIN @srch ws ON ws.CAT=wt.CAT AND ws.VAL=wt.VAL
GROUP by wt.ID) c ON c.ID=r.ID
ORDER BY CNT DESC,CNT_C DESC

お礼

おぉぉぉ～～～
すばらしいです。さすがです。
分かりずらい説明の中、ありがとうごいます。
私の望んでいた通りの結果が出力されました。

組み立て方の発想の問題なんですね。
頭の中がぐちゃぐちゃになり訳の分からないことになっていました。
再度見直し勉強させていただきます。

また、その他アドバイスがありましたら宜しくお願い致します。