mysql - 根据另一个表中的多行搜索一个表

Question

基本上我有三个 MySQL 表:

Users - 包含用户的基本信息
字段 - 描述所述用户的附加字段(例如位置、出生日期等)
数据 - 包含通过指向字段表的链接描述的用户数据

基本设计如下(以下为精简版)

用户:

 ID | username | password | email | registered_date

字段

 ID | name | type

数据:

 ID | User_ID | Field_ID | value

我想做的是根据用户拥有的字段的值搜索用户，例如示例字段可能是:

全名
城镇/城市
邮政编码
等等

我有以下内容，当您只想按一个字段搜索时，它会起作用:

SELECT `users`.`ID`,
       `users`.`username`,
       `users`.`email`,
       `data`.`value`,
       `fields`.`name`

FROM `users`,
     `fields`,
     `data`

WHERE `data`.`Field_ID` = '2'
AND `data`.`value` LIKE 'london'
AND `users`.`ID` = `data`.`User_ID`
AND `data`.`Field_ID` = `fields`.`ID`

GROUP BY `users`.`ID`

但是如果你想搜索多个字段怎么办？例如假设我想搜索城镇/城市设置为“伦敦”的全名“Joe Bloggs”？这是我真正的症结所在。

MySQL 可以实现这样的功能吗？

Answer 1

我假设“搜索多个字段”是指 Entity-Attribute-Value structure .

在这种情况下，我建议第一步是创建一个派生查询 - 基本上，我们希望将“加入的 EAV 数据”限制为仅包括具有我们的值的记录有兴趣找。 (我更改了一些列名称，但前提相同。)

SELECT d.userId
FROM data d
JOIN fields f
  ON f.fieldId = d.fieldId
-- now that we establish data/field relation, filter rows
WHERE f.type = "location" AND d.value = "london"
   OR f.type = "job" AND d.value = "programmer"

结果行源自符合我们条件的过滤 EAV 三元组。在这种情况下仅选择 userId(因为它将用于加入用户关系)，但也可以推送 fieldId/value/etc。

然后我们可以将所有这些用作派生查询:

SELECT * 
FROM users u
JOIN (
  -- look, just goes in here :)
  SELECT DISTINCT d.userId
  FROM data d
  JOIN fields f
    ON f.fieldId = d.fieldId
  WHERE f.type = "location" AND d.value = "london"
     OR f.type = "job" AND d.value = "programmer"
) AS e
ON e.userId = u.userId

注意事项:

1. 查询规划器会非常敏锐地计算出所有 RA 的东西；不要担心这种“嵌套”，因为没有依赖子查询。
2. 我避免使用隐式交叉联接，因为我觉得它们混淆了大多数查询，这种情况就是一个特别好的例子。
3. 我“作弊”并在派生查询中添加了一个 DISTINCT。这将确保每个用户最多加入/返回一条记录，并避免使用 GROUP BY。

---

虽然上面的代码很好地获得了“OR”语义(这既简单又我可能误解了问题)，但需要修改才能获得“AND”语义。这里有一些方法可以编写派生查询来获得这样的查询。 (在这一点上，我必须向托尼道歉——我忘记了我已经完成了所有的管道工作，以便在我的环境中简单地生成此类查询。)

计算匹配的数量以确保所有行都匹配。这仅在每个用户的每个实体都是唯一的时才有效。它还消除了 DISTINCT 维持正确多重性的需要。

SELECT d.userId
FROM data d
JOIN fields f
  ON f.fieldId = d.fieldId
-- now that we establish data/field relation, filter rows
WHERE f.type = "location" AND d.value = "london"
   OR f.type = "job" AND d.value = "programmer"
GROUP BY d.userId
HAVING COUNT(*) = 2

找到相交的匹配项:

SELECT d.userId
FROM data d
JOIN fields f ON f.fieldId = d.fieldId  
WHERE f.type = "location" AND d.value = "london"
INTERSECT
SELECT d.userId
FROM data d
JOIN fields f ON f.fieldId = d.fieldId  
WHERE f.type = "job" AND d.value = "programmer"

使用 JOINS(参见 Tony 的回答)。

SELECT d1.userId
FROM data d1
JOIN data d2   ON d2.userId = d1.userId
JOIN fields f1 ON f1.fieldId = d1.fieldId
JOIN fields f2 ON f2.fieldId = d2.fieldId
-- requires AND here across row
WHERE f1.type = "location" AND d1.value = "london"
  AND f2.type = "job"      AND d2.value = "programmer"

在条件外应用时，内部 JOIN 本身提供合取语义。在这种情况下，我显示“重新规范化”数据。这也可以写成 [sub-]selects 出现在 select 子句中。

SELECT userId
FROM (
  -- renormalize, many SO questions on this
  SELECT q1.userId, q1.value as location, q2.value as job
  FROM (SELECT d.userId, d.value
     FROM data d
     JOIN fields f ON f.fieldId = d.fieldId
     WHERE f.type = "location") AS q1
  JOIN (SELECT d.userId, d.value
     FROM data d
     JOIN fields f ON f.fieldId = d.fieldId
     WHERE f.type = "job") AS q2
  ON q1.userId = q2.userId
) AS q
WHERE location = "london"
  AND job = "programmer"

上述口是心非通过代码相对容易生成，并且一些数据库(例如 SQL Server)支持 CTE，这使得编写变得更加简单。 YMMV.