MySQL-选择带有多个主题标签的所有项目 [英] MySQL - select all items with multiple hashtags

问题描述

我正在为我的网页制作主题标签系统，并且具有三个表:

I'm making hashtag system for my webpage, and have three tables:

• 项目-ID和其他一些
• 标签-ID和名称
• item_tags-具有IDitem和IDtag的联结表

选择带有给定标签的所有项目非常简单:

Selecting all items with given hashtag is pretty easy:

SELECT items.* FROM items
join item_tags on items.ID=item_tags.IDitem
join tags on item_tags.IDtag=tags.ID
where tags.name="something";

问题是，如果我想选择带有多个标签的所有物品，例如，找到所有标记为猫和动物的物品，该怎么办?

Question is, what should I do if I want to select all items with multiple tags, for example, find all items tagged as cat and animal?

我曾考虑过制作临时表，将所有带有第一个标签的项目插入，然后保留带有第二个标签的项目，然后保留第三个，然后是第四个，依此类推，但这看起来并不太好，也不太快.

I've thought about making temporary table, inserting all items with first tag, then leaving those with second tag, then third, then fourth and so on, but it doesn't look too good and too fast.

推荐答案

好，您知道您的列表，所以这是一个简单的字符串.你知道你的计数.这些可以塞进mysql Prepared Statement并执行.

well you know your list, so that is a simple string. and you know your count. these can be jammed into a mysql Prepared Statement and executed.

但是下面是列表，并且仅出于演示目的而添加了计数.

But below it is with the list and count plopped in just for demo purposes.

create table items
(   id int not null
);

create table tags
(   id int not null,
    name varchar(50)
);

create table item_tags
(   iid int not null,
    tid int not null
);

insert items (id) values (1),(2),(3),(4);
insert tags(id,name) values (1,'cat'),(2,'animal'),(3,'has nose');
-- note, everything has a nose so far:
insert item_tags (iid,tid) values (1,1),(1,3),(2,1),(2,3),(3,2),(3,3),(4,1),(4,2),(4,3);

select i.id,count(i.id)
from items i
join item_tags junc
on junc.iid=i.id
join tags t
on t.id=junc.tid and t.name in ('cat','animal')
group by i.id
having count(i.id)=2

-- only item 4 has both cat and animal (note nose is irrelevant)

这篇关于MySQL-选择带有多个主题标签的所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！