MySQL Select ID 出现在不同的行上，一列有多个特定值

Question

我正在尝试从关联表中选择满足同一字段的两个或多个值的项目，这听起来令人困惑，让我解释一下。

+-----------------------+
| item_id | category_id |
+-----------------------+
|   1     |    200      |
|   1     |    201      |
|   1     |    202      |
|   2     |    201      |
|   2     |    202      |
|   3     |    202      |
|   3     |    203      |
|   4     |    201      |
|   4     |    207      |
+-----------------------+

在表格中，我希望能够只选择我通过的类别中的项目。因此，例如，如果我传递 201 和 202 的类别 ID，我只想要同时属于这两个类别的项目(它们可以有其他类别，但至少需要属于我正在查询的类别)，所以在这种情况下，我只想要项目 1 和项目 2，因为它们是类别 201 和 202 中唯一的项目。

我最初的 SQL 语句是这样的

SELECT * FROM item_category WHERE category_id = 201 AND category_id = 202;

但显然那是行不通的。

SELECT * FROM item_category WHERE category_id = 201 OR category_id = 202;

上面的查询也不会工作，因为它也会返回项目 4 和 3。

那么我将如何只选择必须至少属于两个类别的项目？

请记住，我可能会传递 2 个以上的类别 ID。

感谢您的帮助。

Answer 1

您在 WHERE 子句中的表达式适用于连接结果集的单行。这就是为什么 WHERE category_id = 201 AND category_id = 202 不起作用的原因——因为它不能是同一行上的两个值。

因此您需要某种方法将表中的两行连接到结果集中的一行。您可以使用自联接 来做到这一点:

SELECT c1.item_id
FROM item_category AS c1
INNER JOIN item_category AS c2 ON c1.item_id = c2.item_id
WHERE c1.category_id = 201 AND c2.category_id = 202

当您想搜索三个、四个、五个或更多值时，这种技术很难扩展，因为它需要 N-1 连接来匹配 N 个值.

所以另一种方法是使用 GROUP BY:

SELECT c.item_id, COUNT(*) AS cat_count
FROM item_category AS c
WHERE c.category_id IN (201,202)
GROUP BY c.item_id
HAVING cat_count = 2

这两种技术都可以，并且在不同情况下效果更好。