选择与多个标签匹配的项目 [英] Selecting an item matching multiple tags
本文介绍了选择与多个标签匹配的项目的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这似乎很基础,但我无法弄清楚.
This seems very basic but I can't figure it out.
我有一个表"item_tags",我想选择所有与标签1和2匹配的项目(例如,每个项目必须同时具有两个标签).
I've got a table "item_tags", and I want to select all of the items that match tags 1 and 2 (as in, each item has to have both tags).
我该如何在mysql中做到这一点?
How would I do this in mysql?
创建表是:
CREATE TABLE `item_tags` (
`uid_local` int(11) NOT NULL DEFAULT '0',
`uid_foreign` int(11) NOT NULL DEFAULT '0',
`sorting` int(11) NOT NULL DEFAULT '0',
KEY `uid_local` (`uid_local`),
KEY `uid_foreign` (`uid_foreign`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
谢谢!
推荐答案
使用:
SELECT i.uid
FROM ITEMS i
JOIN ITEM_TAGS it ON it.uid_local = i.uid
AND it.uid_foreign IN (1, 2)
GROUP BY i.uid
HAVING COUNT(DISTINCT it.uid_foreign) = 2
您需要定义GROUP BY和HAVING子句,并且不同标签ID的数量必须等于您在IN子句中指定的标签数.
You need to have a GROUP BY and HAVING clause defined, and the count of distinct tag ids must equal the number of tags you specify in the IN clause.
这篇关于选择与多个标签匹配的项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文