mysql - 使用多个 "WHERE IN"和 "OR"搜索 MySql 数据库不起作用

Question

假设我有一个这样的表:

假设我的用户输入他想要查看性别为男性并且眼睛颜色 = 灰色的所有记录。

我已经有以下 SQL:

SELECT User, question, answer FROM [Table] WHERE User IN (
    SELECT User FROM [table] WHERE (question, answer) IN (
        ('gender', 'male'),
        ('eyecolor', 'grey')
    )
)
GROUP BY User
HAVING count(distinct question, answer) = 2)

但是，如果我的用户想要查看 (gender =male OR sex =female) AND eyecolor = grey 的所有记录该怎么办？我如何格式化上面的 sql 查询以使其能够找到该查询？

(请记住，这是一个搜索表单，因此眼睛颜色和性别只是用于搜索的几个字段；我需要能够使用和/或组合进行搜索)

我认为让这个工作的唯一方法是:

SELECT User
FROM [table]
WHERE (gender = male OR gender = female) AND eyecolor = blue

我的 php 必须构建查询，以便如果用户输入更多字段，查询会扩展更多 WHERE 等？

我一直在到处寻找，但一直没能让它发挥作用。不可否认，我不是世界上最擅长这个的。

Answer 1

http://sqlfiddle.com/#!2/2e112/1/0

select * 
from   stuff
where  ID in (   
       select ID
       from   stuff
       where  (question='gender' and answer in ('male','female')) or 
              (question='eyecolor' and answer='grey') 
       group by ID
       having count(ID)=2 
      )

where 2 是嵌套 where 语句中的条件数量。如果您单独运行该嵌套选择，它只会为您提供符合条件的不同 ID 列表。外部语句允许查询返回符合这些条件的 ID 的所有记录。

我编辑了这个，因为......我之前错了

<小时/>

k... http://sqlfiddle.com/#!2/2f526/1/0

select  *
from   stuff
where  (question='gender'   and answer in ('male','female')) or 
       (question='eyecolor' and answer='grey')  or 
       (question='city'     and answer in ('madrid','amsterdam')) 

对于此查询，我们返回与任何 ID 的任何这些条件匹配的一行。只有至少满足其中一个条件的 ID 才会出现在结果中。

select ID, count(*) as matches
from   stuff
where  (question='gender'   and answer in ('male','female')) or 
       (question='eyecolor' and answer='grey')  or 
       (question='city'     and answer in ('madrid','amsterdam')) 
group  by ID;

然后我们添加分组依据，这样我们就可以看到为每个用户返回了多少行以及他们满足了多少个条件 (count(*))。

select ID
from   stuff
where  (question='gender'   and answer in ('male','female')) or 
       (question='eyecolor' and answer='grey')  or 
       (question='city'     and answer in ('madrid','amsterdam')) 
group  by ID
having count(ID)=3;

having count(ID)=3; 是使该查询起作用的原因。我们只想要返回 3 行的 ID，因为我们有 3 个条件。

and....我们不能使用and，因为该表中的任何行都不会同时满足多个条件。 问题不能同时是性别、眼睛颜色和城市。这与你的 table 布局有关。城市永远不会同时是马德里和阿姆斯特丹......和不会给我们任何东西。那么...通过使用having和or...我们可以做一些快乐的事情...？

<小时/>

并继续切线......如果你的 table 看起来像这样:

ID      gender      eyecolor       city         
---------------------------------------------
100     male        blue           madrid
200     female      grey           amsterdam
300     male        brown          somewhere

你会使用和因为......

select  * 
from    table 
where   gender in ('male','female') and
        city in ('madrid','amsterdam') and 
        eyecolor = 'grey'

但是你的表格很特殊，并且不想那样做，因为你真的不应该为每个问题都留一列......如果它们发生变化怎么办，或者如果你添加 20 个怎么办？那将很难维持。

<小时/>

还有......

select ID
from   stuff
where  question in ('gender','eyecolor','city')   and 
       answer in ('male','female','grey','madrid','amsterdam') 
group  by ID
having count(ID)=3; 

确实也有效，但我对此会非常谨慎，因为..问题和答案应该放在一起并且明确，因为..如果这是约会服务怎么办？ male 可以是一个人自己的性别或他们想要约会的性别的答案，通过执行 question='gender' 并回答 ('male','female') 您准确地指定了您的意思，而不是假设某些信息只是一个问题的有效答案。