複数レコードの完全一致

質問

日本語でどう説明すればもわからないのでタイトルも意味不明ですが
以下のテーブルからプリウスとアクアだけを取り出したいのです。

○自動車テーブル○
車名(PK),　連番(PK),　装備,　　数量
---------------------------------
プリウス,　　　1,　　エアコン,　　2
プリウス,　　　2,　　ブレーキ,　　1
アクア　,　　　1,　　ブレーキ,　　1
アクア　,　　　2,　　エアコン,　　2
レクサス,　　　1,　　　テレビ,　　3
レクサス,　　　2,　　　ビデオ,　　2
ノア　　　,　　　1,　　ブレーキ,　　1
ノア　　　,　　　2,　　　　テレビ,　　1

○取得したい結果○
車名
--------
プリウス
アクア

エアコン2個、且つブレーキ1個が装備されている車名を取得したいのです。
ブレーキ1個が装備されているだけのノアはエアコン2個という条件が満たされていないので取得したくありません。

そもそもこのようなことが可能なのかもわからないのですがjava側で処理をするには件数があまりに膨大なので行き詰まっています。

明日、職場からチェックさせていただきます。
お礼は夜になってしまいますが何卒よろしくお願いします。

回答No.2

root139 · ベストアンサー

まず、条件をなるべく素直に書くと↓のようなSQLになりました。

SELECT DISTINCT 車名 FROM 自動車 a
WHERE
EXISTS (
SELECT * FROM 自動車 b
WHERE a.車名 = b.車名 AND 装備 = 'エアコン' AND 数量 = 2
) AND EXISTS (
SELECT * FROM 自動車 c
WHERE a.車名 = c.車名 AND 装備 = 'ブレーキ' AND 数量 = 1
)
;

WHERE句でサブクエリーを使って、条件にマッチする行の存在をチェックしています。

これでは、ちょっと冗長な感じがするのでGROUP BYとHAVINGを使って書き直すと下記の様になりました。

SELECT 車名 FROM 自動車
GROUP BY 車名
HAVING
SUM(CASE WHEN 装備 = 'エアコン' AND 数量 = 2 THEN 1 ELSE 0 END) > 0 AND
SUM(CASE WHEN 装備 = 'ブレーキ' AND 数量 = 1 THEN 1 ELSE 0 END) > 0
;

HAVING句内で集約関数とCASE式を組み合わせる事によって、1番目のSQLと同様の存在チェックを行っています。

蛇足ですが、車種も装備も同一の行が複数有ると思えませんので、連番の行は冗長かと思います。
下記の様に車名と装備の組が主キーとなっても良さそうに見えます。

車名(PK), 装備(PK), 数量

参考URL：

http://codezine.jp/article/detail/652

お礼

詳しくご説明していただきありがとうございます。

このような方法は全く想像できませんでした。
無事に今日一日を終えることができました。
大変助かりましたm(__)m

回答No.1

maiko0318

select name from (select name as name from hoge where soubi='エアコン' and suu=2 union all select name as name FROM hoge where soubi='ブレーキ' and suu=1) group by name having count(*)=2

お礼

すぐにご回答いただきましてありがとうございます。
そして私にはとてもわかりやすく、正直感動してしまいました。
処理が重いのかどうかはわからないのですが、
設計者の私自信が一番わかりやすいと感じたのでこのSQLを使わせていただきました。
ありがとうございました。