WHERE句で3カラム中2カラムに一致する場合のSQL式

Question

とある検索ページを作成中です。
その中に◯◯且つ△△というフォームがあります。
例えば◯◯と△△の趣味検索が出来るフォームがあり、趣味を3つ登録出来るテーブルがあったとし、

SELECT name FROM address WHERE hobby1 IN (?,?) AND hobby2 IN (?,?) AND hobby3 IN (?,?)

これでは3つ中1つには趣味が一致しませんので結果を返しません。
この「3つ中2つに合致する値がある」という場合、どういった式があるのか想像がつかず、質問させていただきました。

ご教授の程お願い致します。

Comments

[liguofeng29]

DBの種類はなんでしょうか？

[takepieee]

MySQLです。MySQLiで作っています。

Answer 1

可読性にやや難ありですが、CASEを使ったこういう書き方もできます。

SELECT
    name
FROM
    address
WHERE
    (CASE WHEN hobby1 IN (?,?) THEN 1 ELSE 0 END) +
    (CASE WHEN hobby2 IN (?,?) THEN 1 ELSE 0 END) +
    (CASE WHEN hobby3 IN (?,?) THEN 1 ELSE 0 END) >= 2

Comments

[takepieee]

ありがとうございます。私の今回のケースはこちらで解決致しました。

[haru666]

このコードだとhobby1とhobby2が両方とも同じ値で、３つ中１つに合致していてもクエリが通ってしまうので気を付けてください。

例
hobby1=1, hobby2=1, hobby3=3 という列は(1,2)で検索した時ヒット対象です
CASE WHEN hobby1 IN(1,2) THEN 1 ELSE 0 END -> 1
CASE WHEN hobby2 IN(1,2) THEN 1 ELSE 0 END -> 1
CASE WHEN hobby3 IN(1,2) THEN 1 ELSE 0 END -> 0

[takepieee]

>haru666様
hobby1, hobby2, hobby3それぞれユニーク値という想定しかしておりませんでした。ご指摘感謝致します。

Answer 2

考え方として、
・hobby1 に指定した趣味があるレコードを取り出す
・hobby2 も同様
・hobby3 も同様
として、それぞれを持つレコードの一覧が出ます。
この3つの一覧の中に、2つ以上重複して出てくるレコードが欲しい結果になりますね。
なので GROUP BY と HAVING で集計してしまえそうです。

下の SQL で UNION ALL ではなく UNION を使っているのは、ないとは思いますがあるレコードで hobby1 と hobby2 とに同じ趣味を書いている場合（その他も同様）を排除するためです。この制限を考慮しなくてよいなら UNION ALL で多少早くなります。

SELECT name
FROM (
  SELECT name, hobby1 AS hobby FROM address WHERE hobby1 IN (?, ?)
  UNION
  SELECT name, hobby2 AS hobby FROM address WHERE hobby2 IN (?, ?)
  UNION
  SELECT name, hobby3 AS hobby FROM address WHERE hobby3 IN (?, ?)
) GROUP BY name HAVING count(*) >= 2

Comments

[yuba]

algさんの回答にもtacsheavenさんの回答にも+1を付けましたが、私の中でより評価の高いのはこちらになります。

どなたも直接ご指摘なさってはいないのですが、テーブル設計には問題があるのです。hobby1, hobby2, hobby3 というカラムを並べていることが問題で、これは避けるべき手法の一つとして「マルチカラムアトリビュート」という名前がついています。
[7章 マルチカラムアトリビュート（複数列属性） - Qiita](http://qiita.com/mizunokura/items/a9be12e0eddcf5d90f07)

つまり長期的にはテーブル構造を改善すべきなのですが、tacsheavenさんの回答は改善後のテーブル構造をその場で再構成して、それに対して問い合わせているクエリです。ということは、テーブル構造改善後もそのまま使っていけるということになります。
この将来を考えた解決が◎と感じた次第であります。

[takepieee]

ありがとうございます。

[takepieee]

>yuba様
ありがとうございます。
非常に分かりやすい解説でした。

[haru666]

欠点としては重たいクエリですし、他の条件を合わせて使いたい時には都合が悪いクエリなので、将来性は完璧だけど現行のパッチとしては今一という感じがします。

[alg]

yubaさんのコメントに「なるほどー」と感心しました。
実業務でもhobby1から20まであるようなテーブルをたまに見かけますし、そのたびにモヤモヤしています。
が、それはそれとして受け入れてしまうしかないケースがほとんどなので、テーブル構造改善までは思い至りませんでした。
後々のテーブル構造改善まで見すえたtacsheavenさんの回答、素晴らしいですね。
それと私の回答には解説がないので、その点でもtacsheavenさんの回答の方が優れていると思います。

Answer 3

これはhobbyを３カラムに分けずに、１カラムで管理し
正規化すれば一発で解決します

Comments

[takepieee]

確かにそうですね。php以前の問題ですね。

[ikuwow]

他の回答で紹介されているような方法でできなくもないですが、
根本的にはやはり設計が正規化されていないのが原因ですね。

Answer 4

それぞれの組み合わせで検索を行い取得したデータを結合すれば良いと思いますよ。

SELECT name FROM address WHERE hobby1 IN (?,?) AND hobby2 IN (?,?)
UNION
SELECT name FROM address WHERE hobby1 IN (?,?) AND hobby3 IN (?,?)
UNION
SELECT name FROM address WHERE hobby2 IN (?,?) AND hobby3 IN (?,?)

Comments

[takepieee]

ありがとうございます。
実際の検索項目は20項目程度の検索項目があり、その入力内容次第で可変プレースホルダを条件分岐で設定していますので、頭が混乱しています。。
少し頭を整理します。

Answer 5

解決策１. EXISTS句を使う
利点：条件を追加しやすい
パラメータ化するとき、上下で１個目の趣味、２個目の趣味を分けてどちらかに存在することを調べます。
（例えば３つのhobbyのうち１つ一致も２つ一致も、WHERE句を連結するだけでよい）
欠点：テーブルの捜査回数が心配。正規化してインデックス張って使えば大分改善します。

SELECT name
FROM address T
WHERE EXISTS(SELECT * FROM address WHERE T.id=id AND (hobby1=? OR hobby2=? OR hobby3=?))
AND EXISTS(SELECT * FROM address WHERE T.id=id AND (hobby1=? OR hobby2=? OR hobby3=?))

解決策２．ネストされたOR句を使う
利点：早い。
欠点：読みづらい＝クエリを構築しづらい。条件の変化で構築すべきクエリがかわる。

SELECT name
FROM address T
WHERE
((hobby1=? AND (hobby2=? OR hobby3=?) OR
(hobby2=? AND (hobby1=? OR hobby3=?) OR
(hobby3=? AND (hobby1=? OR hobby2=?))

パラメータが？だと分かりにくいので追記

SELECT name
FROM address T
WHERE
((hobby1=@hobby1 AND (hobby2=@hobby2 OR hobby3=@hobby2) OR
(hobby2=@hobby1 AND (hobby1=@hobby2 OR hobby3=@hobby2) OR
(hobby3=@hobby1 AND (hobby1=@hobby2 OR hobby2=@hobby2))

Comments

[takepieee]

ありがとうございます。

Answer 6

テキストエディターで書いたので脱字があるかも知れません。

まず、oracleの場合

-- ○○を持つ人
SELECT name FROM address 
WHERE hobby1 = ○○ or hobby2 = ○○ or hobby3 = ○○ 
INTERSECT -- 重複する人を選ぶ
-- △△を持つ人
SELECT name FROM address 
WHERE hobby1 = △△ or hobby2 = △△ or hobby3 = △△ 

mysqlの場合、intersect実現

SELECT name from address add1
WHERE hobby1 = ○○ or hobby2 = ○○ or hobby3 = ○○ 
EXISTS (
	SELECT name from address add2
	WHERE hobby1 = △△ or hobby2 = △△ or hobby3 = △△ 
	and add2.name = add1.name
)

もちろん、nameは主キーではないので、
主キーで結合を行うべきでしょうね。

Answer 7

下記の方針でいけると思いますよ。
1-WHERE句は [趣味1=hoge or 趣味2=hoge or 趣味3=hoge]
2-SELECT句でCASEを使い趣味1、2、3がhogeと一致するなら1にする。
3-2の値の合計が2以上の物を抽出する。

select name from (select [2の内容] where [1の内容] from hoge) as hoge where [3の内容]

Comments

[takepieee]

ありがとうございます。

[yona]

カラムがたくさんだとダメかもしれないです。

Answer 8

本来は正規化が求められるのは置いておいて…。

yuba 様のコメントにある
7章 マルチカラムアトリビュート（複数列属性） - Qiita
を読んだのですが、

SELECT * FROM Bugs
WHERE 'performance' IN (tag1, tag2, tag3)
  AND 'printing' IN (tag1, tag2, tag3);

という書き方が許されるようで、

SELECT name FROM address WHERE ? IN (hobby1,hobby2,hobby3) AND ? IN (hobby1,hobby2,hobby3);

のようにしても実現できるかもしれませんね。無駄な処理は少なそうです。

Comments

[takepieee]

すごいですね。これでも行けました。
可読性も高く、プレースホルダも1/3で済みます。
測ってませんが早そうな気がします。
ありがとうございました。