连接：三个表和一个或条件

Question

我想我应该以某种方式知道这一点，特别是在阅读了很多有关“条件必须进入 ON 子句，而不是 WHERE 子句”的问题和答案之后。然而，我还是迷失了。

我有三个表，通常使用左（外部）连接来连接它们。连接的表看起来像这样（retty 标准）：

task_id task_questions_taskId   taskQuestions_questionId    question_id
1         1                     5                           5
1         1                     8                           8
2         2                     8                           8

SELECT `t`.`id` AS `task_id` , 
       `task_questions`.`taskId` AS `task_questions_taskId` ,
       `task_questions`.`questionId` AS `task_questions_questionId` , 
       questions.id AS question_id
FROM `task` `t`
LEFT OUTER JOIN `task_questions` `task_questions` 
    ON ( `task_questions`.`taskId` = `t`.`id` )
LEFT OUTER JOIN `question` `questions` 
    ON ( `task_questions`.`questionId` = `questions`.`id` )

这是获取所有记录的标准查询。 （它取自 Yii；我实际上想使用 Active Record 来实现这一点，但甚至无法获得简单的 SQL）。

现在我只想获取那些具有 Question_id 2 和 8 的任务（例如） 因此，如果任务不具有这两个 Question.ids，我不希望它出现在结果集中。 在这种情况下，任务也可以有其他question_id。尽管如果查询只返回那些恰好具有这 2 个（或任何其他集合）的查询，那么看看查询会是​​什么样子会很有趣。 很容易获得所有有一个问题的任务，使用 WHERE Question.id = 2， 但 WHERE 子句中的 AND 会导致空结果。

Answer 1

WHERE 子句一次只能将条件应用于一行。但是您不同 id 的问题出现在不同的行上。怎么解决这个问题呢？使用自连接将两行连接到一行。

下面是一个示例：

SELECT t.`id` AS `task_id`, ...
FROM `task` AS t
INNER JOIN `task_questions` AS tq2 ON ( tq2.`taskId` = t.`id` )
INNER JOIN `questions` AS q2 ON ( tq2.`questionId` = q2.`id` )
INNER JOIN `task_questions` AS tq8 ON ( tq8.`taskId` = t.`id` )
INNER JOIN `questions` AS q8 ON ( tq8.`questionId` = q8.`id` )
WHERE q2.`id` = 2 AND q8.`id` = 8

另一个解决方案是查找具有问题 2 OR 8 的任务，然后使用 GROUP BY 和 HAVING 按正好包含其中两个问题的组进行筛选。

SELECT t.`id` AS `task_id`, ...
FROM `task` AS t
INNER JOIN `task_questions` AS tq ON ( tq.`taskId` = t.`id` )
INNER JOIN `questions` AS q ON ( tq.`questionId` = q.`id` )
WHERE tq.`questionId` IN (2, 8)
GROUP BY t.`id`
HAVING COUNT(DISTINCT q.`id`) = 2

Answer 2

即使不使用和，你也可以做到这一点
...其中 Question.id IN (2,8)

Answer 3

使用IN：

SELECT `t`.`id` AS `task_id` , 
       `task_questions`.`taskId` AS `task_questions_taskId` ,
       `task_questions`.`questionId` AS `task_questions_questionId` , 
       questions.id AS question_id
FROM `task` `t`
LEFT OUTER JOIN `task_questions` `task_questions` 
    ON ( `task_questions`.`taskId` = `t`.`id`)
LEFT OUTER JOIN `question` `questions` 
    ON ( `task_questions`.`questionId` = `questions`.`id` )
WHERE  `task_questions`.`questionId` IN (2, 8)

Answer 4

这应该可以做到

SELECT `t`.`id` AS `task_id` , 
       `task_questions`.`taskId` AS `task_questions_taskId` ,
       `task_questions`.`questionId` AS `task_questions_questionId` , 
       questions.id AS question_id
FROM `task` `t`
LEFT OUTER JOIN `task_questions` `task_questions` 
    ON ( `task_questions`.`taskId` = `t`.`id` )
LEFT OUTER JOIN `question` `questions` 
    ON ( `task_questions`.`questionId` = `questions`.`id` )
WHERE  questions.id in (2,8)

Answer 5

您不是在寻找 AND，而是在寻找 OR 或 IN：

WHERE `questions`.`id` IN (2,8) -- grab everything in the parens.

或者

WHERE `questions`.`id` = 2 OR -- grab each item individually
      `questions`.`id` = 8

如果您使用 AND，则意味着 ID 必须同时为 8 和 2。糟糕的交易。