SQL“where IN”查询在2个表的多对多关系中

Question

我可能会问一个相对简单的问题。但我找不到解决办法。这是两张桌子的问题，它们之间有第三张桌子。下面的模式：

CREATE TABLE `options` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `name` varchar(200) NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO `options` (`id`, `name`) VALUES 
(1, 'something'), 
(2, 'thing'), 
(3, 'some option'), 
(4, 'other thing'), 
(5, 'vacuity'), 
(6, 'etc'); 

CREATE TABLE `person` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `name` varchar(200) NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO `person` (`id`, `name`) VALUES 
(1, 'ROBERT'), 
(2, 'BOB'), 
(3, 'FRANK'), 
(4, 'JOHN'), 
(5, 'PAULINE'), 
(6, 'VERENA'), 
(7, 'MARCEL'), 
(8, 'PAULO'), 
(9, 'SCHRODINGER'); 

CREATE TABLE `person_option_link` (
    `person_id` int(11) NOT NULL, 
    `option_id` int(11) NOT NULL, 
    UNIQUE KEY `person_id` (`person_id`,`option_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 


INSERT INTO `person_option_link` (`person_id`, `option_id`) VALUES 
(1, 1), 
(2, 1), 
(2, 2), 
(3, 2), 
(3, 3), 
(3, 4), 
(3, 5), 
(4, 1), 
(4, 3), 
(4, 6), 
(5, 3), 
(5, 4), 
(5, 5), 
(6, 1), 
(7, 2), 
(8, 3), 
(9, 4) 
(5, 6); 

这个想法如下：我想检索所有谁有一个链接到option_id = 1和option_id = 3的人。

预期结果应该是一个人：约翰。

但我试图用类似的东西，这是不行的，因为它也返回谁拥有1或3人：

SELECT * 
FROM person p 
LEFT JOIN person_option_link l ON p.id = l.person_id 
WHERE l.option_id IN (1, 3) 

什么是在这种情况下，最好的做法是什么？

//////// POST EDITED：我需要关注其他重点//////// 如果我们在NOT IN中添加一个新条件会怎样？像：

SELECT * 
FROM person p 
LEFT JOIN person_option_link l ON p.id = l.person_id 
WHERE l.option_id IN (3, 4) 
AND l.option_id NOT IN (6) 

在这种情况下，结果应该坦率地说，因为PAULINE谁也已3和4，可以选择6，我们不希望出现这种情况。

谢谢！

Answer 1

它可能不是最好的选择，但你可以使用“双重加入”到person_option_link表：

SELECT * 
    FROM person AS p 
    JOIN person_option_link AS l1 ON p.id = l1.person_id AND l1.option_id = 1 
    JOIN person_option_link AS l2 ON p.id = l2.person_id AND l2.option_id = 3 

这可确保同时有与1选项ID一排，另一个选项给定用户的ID为3。

GROUP BY的替代品肯定有效;他们可能会更快（但您需要仔细检查查询计划以确保）。 GROUP BY替代方法可以更好地处理更多值：例如，具有选项ID 2,3,5,7,11,13,17,19的用户列表很容易使用此变体，但GROUP BY变体无结构地工作对查询的更改。您还可以使用GROUP BY变体来选择8个值中至少有4个值的用户，这种方法基本上不可行。

使用GROUP BY确实需要一个轻微的重述（或反思）查询，不过，到：

• 如何选择谁可以选择ID的2集合{1人，3 }？
• 如何选择在集合{2,3,5,7,11,13,17,19}中具有8个选项ID的人员？
• 如何选择在集合{2,3,5,7,11,13,17,19}中至少有4个选项ID的人员？

Answer 2

这是一个Relational Division问题。

SELECT p.id, p.name 
FROM person p 
     INNER JOIN person_option_link l 
      ON p.id = l.person_id 
WHERE l.option_id IN (1, 3) 
GROUP BY p.id, p.name 
HAVING COUNT(*) = 2 

• SQLFiddle Demo

如果唯一约束不上option_id执行每一个id，一个DISTINCT关键字是必需的过滤独特option_ID

SELECT p.id, p.name 
FROM person p 
     INNER JOIN person_option_link l 
      ON p.id = l.person_id 
WHERE l.option_id IN (1, 3) 
GROUP BY p.id, p.name 
HAVING COUNT(DISTINCT l.option_id) = 2 

• SQL of Relational Division

Answer 3

使用GROUP BY和COUNT：

SELECT p.id, p.name 
FROM person p 
LEFT JOIN person_option_link l ON p.id = l.person_id 
WHERE l.option_id IN (1, 3) 
GROUP BY p.id, p.name 
HAVING COUNT(Distinct l.option_id) = 2 

我更喜欢使用COUNT的情况下，DISTINCT你可以有相同的选项ID多次。

祝你好运。

Answer 4

对于“没有这些ID”问题的一部分，只需添加一个WHERE子句：

WHERE person_id NOT IN 
(
SELECT person_id 
FROM person_option_link 
WHERE option_id = 4 
)