基于一对多关系构建 SQL 查询

Question

我有一个搜索页面，我试图在两个表上构建复杂的搜索条件，看起来像：

Users
ID     NAME
1      Paul
2      Remy

...

Profiles
FK_USERS_ID   TOPIC      TOPIC ID
1             language   1
1             language   2
1             expertise  1
1             expertise  2
1             expertise  3
2             language   1
2             language   2

第二个表配置文件，列出了每个用户的“语言”或“专业知识”（以及其他内容），主题 id 是另一个表的外键，具体取决于主题（如果主题是“语言”，则主题 ID 是语言表中语言的 ID，等等...）。

搜索需要找到类似 的内容，其中用户名 LIKE %PAU% 且用户“拥有”语言 1 且拥有语言 2、拥有专业知识 1 且拥有专业知识 2。

任何帮助将不胜感激！我正在两个表上执行LEFT JOIN，尽管我不确定这是正确的选择。我的主要问题在于“AND”。同一用户必须同时具备语言 1 和 2，并且同时具备专业知识 1 和 2。

我使用 PHP 工作，通常会尝试避免内部 SELECT 甚至联接，但我认为内部 SELECT 即将到来？

Answer 1

您可以通过构建一组与您的个人资料表中的条件相匹配的用户来实现此目的，如下所示：

SELECT FK_USERS_ID 
FROM Profiles 
WHERE topic='x' 
AND TOPIC_ID IN (1,2) 
GROUP BY FK_USERS_ID 
HAVING COUNT(1) = 2

在此处列出与您需要的主题相匹配的用户。通过按用户 ID 分组并指定应返回的行数，您可以有效地说“仅那些在主题 z 中具有 x 和 y 的行。只需确保 COUNT(1) = x 具有相同数量的不同行 解释

然后您可以查询用户表

SELECT ID 
FROM Users 
WHERE name like '%PAU%' 
AND ID IN (<insert above query here>)

您也可以在联接和派生表中执行此操作，但其本质应在上面进行

：
如果你正在寻找多种组合，你可以使用mysql的多列IN：

SELECT FK_USERS_ID 
FROM Profiles 
WHERE (topic,topic_id) IN (('x',3),('x',5),('y',3),('y',6))
GROUP BY FK_USERS_ID 
HAVING COUNT(1) = 4

这将寻找与x-3、x-5、y-3和y-6对匹配的用途。

您应该能够在 php 中轻松构建 topic-topic_id 对并将其填充到 SQL 字符串中，并且只需将生成的对数计数到变量中以用于 count(1) 数字。请参阅http://www.mysqlperformanceblog .com/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/ 使用此方法进行性能讨论。

Answer 2

不就是一个简单的经典INNER JOIN吗？

SELECT
  p.topic, p.topic_id
FROM
  profiles p
INNER JOIN
  users u
ON
  u.id = p.fk_users_id
WHERE
  u.name LIKE '%Paul%'

此查询将返回与模式匹配的用户的所有语言和专业知识及其 ID，在本例中，用户的名字中包含 Paul。这是你喜欢的吗？或者其他什么？

Answer 3

select *
from users u, profiles p
where u.id = p.fk_users_id
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic = 'language' 
            and topic_id = 1)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic = 'language' 
            and topic_id = 22)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic = 'expertise' 
            and topic_id = 1)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic = 'expertise' 
            and topic_id = 1)
and u.name like '%PAU%'

编辑：

好的，@cairnz 的答案略有不同：

SELECT ID 
FROM Users 
WHERE name like '%PAU%' 
AND ID IN (SELECT FK_USERS_ID 
           FROM Profiles 
           WHERE topic='x' 
           AND ((TOPIC_ID = 1 AND TOPIC = 'language') 
                OR (TOPIC_ID = 2 AND TOPIC = 'language')
                OR (TOPIC_ID = 1 AND TOPIC = 'expertise')
                OR (TOPIC_ID = 2 AND TOPIC = 'expertise'))
           GROUP BY FK_USERS_ID 
           HAVING COUNT(1) = 4)

Answer 4

我会根据您“需要”的每个条件多次执行 JOIN 条件。我还将确保基于要查找的键的每个部分在配置文件表上建立索引...（FK_User_ID、Topic_ID、主题）

SELECT STRAIGHT_JOIN
      U.ID 
   FROM Users U
      JOIN Profiles P1
         on U.ID = P1.FK_User_ID
         AND P1.Topic_Id = 1
         AND P1.Topic = "language"
      JOIN Profiles P2
         on U.ID = P2.FK_User_ID
         AND P2.Topic_Id = 2
         AND P2.Topic = "language"
      JOIN Profiles P3
         on U.ID = P3.FK_User_ID
         AND P3.Topic_Id = 1
         AND P3.Topic = "expertise"
      JOIN Profiles P4
         on U.ID = P4.FK_User_ID
         AND P4.Topic_Id = 2
         AND P4.Topic = "expertise"
   WHERE
      u.name like '%PAU%' 

这样，提供的其他答案中表达的任何其他标准都不会有太大影响。这些表是按标准设置的，就好像同时进行一样，如果有任何缺失，它们将立即从结果中排除，而不是尝试对每个条目进行子选择计数（我认为这可能是您遇到的滞后） 。

因此，您的每个“必需”条件都将采用相同的“JOIN”构造，正如您所看到的，我只是增加连接实例的“别名”。