PHP/MySQL: 多对多/交集表问题

Question

我不太确定如何表达问题，所以让我举一个例子来说明问题：

假设有一个将物品映射到类别的表格。每个物品可以有任意数量的类别，而每个类别当然可以容纳任意数量的物品。因此，您拥有一个如下所示的表格：

items_categories

id item_id category_id

问题是，我想选择具有特定类别ID的所有项目ID。例如，选择具有类别ID为1和2的所有item_id：我想找到与类别1和2都相关联的所有项目。显然，我不能使用AND语句，而OR语句将返回具有任一类别的所有item_id，但不一定同时具有两者。

这是我的解决方案和我能想到的最好的方法：选择所有category_id等于1或2的item_ids；在PHP中迭代结果并跟踪与category_id关联的item_ids的数量；然后取消设置结果中没有指定类别数量的item_ids。以下是我的代码片段：

// assume $results is an array of rows from the db
// query: SELECT * FROM items_categories WHERE category_id = 1 OR category_id = 2;
$out = array();
foreach ($results as $result)
{
    if (isset($out[$result['item_id']]))
        $out[$result['item_id']] ++;
    else
        $out[$result['item_id']] = 1;
}
foreach ($out as $key=>$value)
{
    if ($value != 2)
        unset($out($key));
}
return array_keys($out); // returns array of item_ids

很明显，如果您有许多不同的类别，那么您选择和处理的信息比理论上需要的要多得多。有什么想法吗？

谢谢！

编辑：这里是一个表格的例子和我想要的信息：

id item_id category_id
1 1 1
2 1 2
3 2 1
4 3 2

假设我想获取所有类别为1和2的物品。如果我只想要类别为1和2的物品，如何从我的示例表中获取物品#1？如果我选择具有类别1或2的所有内容（如上面的示例），在这种情况下，我必须选择整个表格并“手动”删除item_id为2和3，因为它们与类别1和类别2都没有关联。希望这能够稍微澄清一下。

最终编辑：尽管我似乎无法描述我正在尝试做什么，但我想出了解决方法。以下是我想出的查询语句，供记录：

SELECT *
FROM
(
    SELECT item_id, COUNT(*) as count
        FROM items_categories
        WHERE category_id IN (1, 2)
    GROUP BY item_id
) table_count
WHERE count = 2;

在这种情况下，"(1, 2)"可以替换为"(category_id1, category_id2, …)"，末尾的"2"将被替换为我要搜索的类别数。因此，它会查找符合每个项目条件的类别数量，由于我只想选择所有类别都匹配的项目，所以它只选择类别数等于我要查找的类别数的那些项目。当然，这是假设没有重复的类别或其他类似情况。感谢您的回复！

- user599599

Answer 1

看起来困扰你的是你被迫进行线性搜索，这当然需要 O(n) 的时间，但如果你按照顺序从数据库中选择元素，那么你难道不能只用 O(lg n) 的时间使用二分搜索吗？

我希望这可以帮到你。如果不能，请告诉我，也许我误解了你的问题，我想请你进一步澄清。

- Jeffrey Greenham

---

谢谢您的回复。问题是，我不是在结果中寻找特定的值——我只想知道每个项目与多少类别相关联。如果该数字与我正在搜索的类别数不同，那么我就会知道该项目不包括所有类别。如果我理解正确，我认为二进制搜索并不能实现这一点，因为我不是在寻找特定的值。 - user599599

顺便说一下，我编辑了我的原始帖子，希望能更清楚一些。 - user599599

Answer 2

SELECT
 foo
FROM
 bar
WHERE
foo IN (1,2) 

这是你要找的吗？

- Brandon Frohbieter

---

那基本上就是通过选择 foo=1 OR foo=2 来实现我的意思。换句话说，这样更清晰明了，但我必须在 PHP 中做同样数量的数组处理来过滤出“foo”不在 1 AND 2 中的结果。（当然，这是不可能的，因为对于任何给定行，“foo”只有一个值，但在我的情况下，我期望多行具有相同的 item_id 和一组 category_id。） - user599599

Answer 3

这是应该由数据库而不是 PHP 来完成的事情。

SELECT item_id                 # We want a list of item ids
FROM cat_items                 # Gets the item ID list from the cat_items table
WHERE cat_id IN (1, 2, 7, 11)  # List of categories you want to search in
GROUP BY item_id;              # As the same item can appear in more than one category this line will eliminate duplicates

这个查询假设cat_items中的数据是准确的，换句话说，类别和项目ID指向类别表和项目表中的有效条目。如果您正在使用支持外键的数据库（MySQL的InnoDB引擎，Postgres等），强制执行外键并不困难。

要按所需格式获得每个类别中的ID列表，在SQL端也很容易完成。

SELECT * 
FROM cat_items 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY cat_id, item_id;

如果您只是想知道每个类别中有多少项，您也可以在 SQL 中实现。

SELECT cat_id, COUNT(item_id) AS items
FROM cat_items 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY cat_id;

如果您需要的数据不仅仅是ID，那么您可以连接到需要数据的表。

SELECT items.* 
FROM cat_items 
JOIN items ON cat_items.item_id = items.id 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY item_id;

- GordonM

---

谢谢回复 - 问题是，这将返回属于任何给定类别的项目，而我只对属于所有类别的项目感兴趣。无论如何，你的计数查询让我思考并解决了这个问题。我只需要计算每个项目匹配的类别数量，并确保它等于我要查找的类别数量。 - user599599

Answer 4

SELECT item_id FROM items_categories WHERE category_id = 1 AND item_id IN (SELECT item_id FROM items_categories WHERE category_id = 2)

- josemimg

---

这正是我想要它做的！然而，当我对我的测试数据库运行它（1000+项）时，它似乎有点慢。我在原始帖子中的最终查询执行了相同的操作，但我找到了如何使其更快的方法。 - user599599