在同一张表中使用多个条件进行SQL选择查询

Question

我在MySQL中有2个表，第一个表有2列：ID和name，第二个表有3列：firstTableId（第一张表的外键），key和value。

我在第一个表中有以下行：

1,Bob 2,Alice 3,Fred

我在第二个表中有以下行：

1,age,20 1,gender,male 2,age,20 2,gender,female 3,age,18 3,gender,male 我想编写一个查询语句，仅使用第二个表上的最后两列（key和value），该查询应该只返回第一个表中的Bob，但我似乎无法弄清楚。

基本上，我想从第二个表中选择所有行，在这些行中，“key = age”和“value = 20”为一行，“key = gender”和“value = male”为另一行。 有没有人能给我指点一下？不推荐操作表结构，因为这是一个简化的示例，第二个表中“key”和“value”列几乎可以是任何东西，它实际上并不限于“age”和“gender”。

提前感谢您。

- Pierre P.

---

你可能想要使用IN和子查询（可以使用丑陋的连接，但这些往往更糟） - okaram

顺便说一下，这是一个可怕的数据库设计。访问复杂而缓慢，没有有用的索引，没有约束条件，不能区分“可以”和“必须”的值...采用这种通用方法会阻碍DBMS帮助您。 - Thorsten Kettner

好的，但是考虑到“key”和“value”都必须完全通用（这两列的任何值都可能出现），并且表1和表2之间必须存在一对多关系，那么哪种设计更好呢？ - Pierre P.

我不明白为什么这些列可以取任何值。年龄=“汽车”或性别=“香肠”实际上意味着什么？首先，表1除了人以外还能包含其他内容吗？否则，为什么表格不只包含两列年龄和性别？（这样可以确保年龄是数字，“男性”或“女性”，或者对于“未知”为空，而不会因错误而变成“邮件”:-) 另一方面，如果表1 也可以包含宠物和喜欢的颜色，我很难想象数据库的用途。我同意有些情况下通用表是可以接受的，但这种情况很少见。 - Thorsten Kettner

上面的例子是简化的，实际上与人、年龄或性别无关，它是各种数据类型的不受限制的不同属性集合（由键标识），我需要能够针对这些属性进行查询的能力。我需要的是，在表1中的每个记录中，一个可以进行查询的键值对映射。所有表1记录的所有映射的键集的结果集（即所有映射的键集的并集）是不受限制的，并且随着时间的推移可能会增长。这种数据结构符合该要求。 - Pierre P.

Answer 1

您可以使用自连接来完成此操作，如下所示：

select
  *
from
  table1 t1
  inner join table2 age on t1.id = age.id
  inner join table2 gender on t1.id = gender.id
where
  (age.`key` = 'age' and age.value = 20)
  and 
  (gender.`key` = 'gender' and gender.value = 'male')

你可能想尝试的另一种策略是使用PIVOT查询。MySQL没有本地支持Pivot的任何内容，但有几个 示例可以参考。

你可以在这个fiddle中看到它的工作原理。

- crthompson

---

没错，这正是我想要的，而且由于这些子句的数量相对有限（现在最多只有3个），性能影响应该是可以接受的，谢谢 :) 点赞。 - Pierre P.

Answer 2

使用两个IN子句（或两个EXISTS子句）。

select *
from table1
where id in (select firstTableId from table2 where key = 'age' and value = '20')
and id in (select firstTableId from table2 where key = 'gender' and value = 'male');

使用EXISTS：

select *
from table1
where exists (select * from table2 where key = 'age' and value = '20' and firstTableId = table1.firstTableId)
and exists (select * from table2 where key = 'gender' and value = 'male' and firstTableId = table1.firstTableId);

- Thorsten Kettner