質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.50%

  • PHP

    20359questions

    PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

  • MySQL

    5856questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

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

解決済

回答 8

投稿

  • 評価
  • クリップ 3
  • VIEW 1,564

takepieee

score 666

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

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

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • liguofeng29

    2017/02/14 11:28

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

    キャンセル

  • takepieee

    2017/02/14 11:30

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

    キャンセル

回答 8

checkベストアンサー

+12

可読性にやや難ありですが、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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/14 11:50

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

    キャンセル

  • 2017/02/14 11:56

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


    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

    キャンセル

  • 2017/02/14 12:04

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

    キャンセル

+11

考え方として、
・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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/14 11:45

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

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

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

    キャンセル

  • 2017/02/14 11:46

    ありがとうございます。

    キャンセル

  • 2017/02/14 11:52

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

    キャンセル

  • 2017/02/14 11:58

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

    キャンセル

  • 2017/02/14 12:00

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

    キャンセル

+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 (?,?)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/14 11:29

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

    キャンセル

+4

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/14 11:48

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

    キャンセル

  • 2017/02/20 13:48

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

    キャンセル

+2

下記の方針でいけると思いますよ。
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の内容]

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/14 11:44

    ありがとうございます。

    キャンセル

  • 2017/02/14 11:47

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

    キャンセル

+2

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

まず、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は主キーではないので、
主キーで結合を行うべきでしょうね。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+2

解決策1. EXISTS句を使う
利点:条件を追加しやすい
パラメータ化するとき、上下で1個目の趣味、2個目の趣味を分けてどちらかに存在することを調べます。
(例えば3つのhobbyのうち1つ一致も2つ一致も、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=?))

解決策2.ネストされた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))

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/14 11:49

    ありがとうございます。

    キャンセル

+1

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

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);

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/22 16:07

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.50%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • PHP

    20359questions

    PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

  • MySQL

    5856questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。