假设我有这样一个表:
item_id tag_id
------- ------
1 1
1 2
2 2
2 3
你可以想象，这是一个表，我有一些项目和属于他们的标签的引用。一个项目可以有多个标签，一个标签可以为多个项目选择。
假设我也有一个特定的标签集合(f.ex)。tag_id = 50,73和119)和一个id为"item_id"的"items"表。
是否有一个有效的查询给我:
- 带有这些标签的物品的计数
- 物品本身?
What I've try
SELECT COUNT(*) FROM
(
SELECT COUNT(*) AS c FROM items_tags it JOIN items i ON i.id = it.item_id
WHERE (tag_id=7 OR tag_id=95 OR tag_id=150) AND `status`='active'
GROUP BY item_id
) t1 WHERE c=3 <-- c= number of tags
我可以有两个结果，但与一个非常(似乎)效率低下的查询。在用EXPLAIN检查后，我想摆脱ORs给出的"范围"。
改进我的问题:问题是我得到了一个写得很差的PHP框架，它通过各种标签id迭代了900多次。假设您有一个或多个固定的id(选定的标签)，它遍历所有900多个标签，以查找具有给定标签加迭代标记的共同项的出现次数(这是一个优化搜索的函数，只显示具有所有给定标签加1的元素)。
给定的代码是这样工作的:我选择一个或多个标签，它们的ID进入querystring。假设我选择了标签54和77。代码必须为同时具有54和77标签的项目找到每个项目ID，并逐一列出它们:我们获得"带有选定标签的项目"列表。
然后，它提供了优化搜索的选项，这里是奇怪的部分:PHP代码循环遍历所有900多个标记，对于每次迭代，它都取一个标记，并计算有多少项具有所有标记54,77和迭代中的1。如果计数> 0，则显示带有计数号的标签的名称，过滤掉所有与所选标签没有任何链接的标签。
如果能以一种不那么"密集"的方式达到同样的结果就好了。
要获取与所有标签匹配的项目id列表，您可以使用以下查询:
SELECT items.id
FROM items
JOIN items_tags ON items.id = items_tags.item_id
WHERE (items_tags.tag_id IN (7,95,150))
AND (items.status = 'active')
GROUP BY items.id
HAVING COUNT(DISTINCT items_tags.tag_id) = 3
请注意，如果您确定没有相同项目的重复标签，您可以用COUNT(*)代替COUNT(DISTINCT items_tags.tag_id)以提高效率。
要获得这些项的计数，请将其包装在count查询中:
SELECT COUNT(*)
FROM (
SELECT items.id
...
) t
要获得项目列表，请将其封装在以下SELECT查询中:
SELECT *
FROM items
WHERE id IN (
SELECT items.id ...
)
要获得每个剩余标签与原始列表组合时的项目计数，可以这样做:
SELECT tag_id, COUNT(DISTINCT item_id)
FROM items_tags
WHERE item_id IN (
SELECT items.id
...
)
AND tag_id NOT IN (7,95,150)
GROUP BY tag_id