MySQL查询来搜索带有特定标签的项目 [英] MySQL query to search for items with certain tags

问题描述

我有一个具有以下表的MySQL数据库:

I have a MySQL database with the following tables:

items      | id, item
items_tags | id, item_name, item_id, tag_name, tag_id
tags       | id, tag

我想允许用户搜索具有任何标签或标签组合的商品.以下是一些示例数据，显示了我想做的事情:

I'd like to allow the user to search for items with any tag or any combination of tags. Here's some example data to show what I'd like to do:

items:

id | item
-----------
1  | banana
2  | orange
3  | tomato

items_tags:

id | item_name | item_id | tag_name | tag_id
---------------------------------------------
1  | banana    | 1       | yellow   | 1
2  | banana    | 1       | fruit    | 2
3  | orange    | 2       | orange   | 3
4  | orange    | 2       | fruit    | 2
5  | tomato    | 3       | red      | 4
6  | tomato    | 3       | vegetable| 5    

tags:

id | tag
--------------
1  | yellow
2  | fruit
3  | orange
4  | red
5  | vegetable

我可以运行什么查询以仅返回标有"yellow"的项目?和水果" (即应返回1个项目的 row 个项目)?

What query could I run to only return items tagged with "yellow" and "fruit" (i.e., should return row 1 of items)?

谢谢！

这是可行的答案:

SELECT * 
  FROM items 
 WHERE id IN (
              SELECT item_id
                FROM items_tags
               WHERE tag_name IN ('yellow', 'fruit')
            GROUP BY item_id
              HAVING COUNT(*) = 2
             )

感谢chetan的帮助！

Thanks to chetan for the help!

推荐答案

如果您希望该项目带有两个标签中的任何一个，则:

if you want the item with any of the two tag then:

select distinct item_id, item_name 
from items_tags 
where tag_name in ('yellow', 'fruit'); 

如果您希望该项目同时具有两个标签，则:

if you want the item having both tag then:

select item_id, item_name 
from items_tags 
where tag_name id ('yellow', 'fruit')
group by item_id, item_name
having count(*) = 2; 

根据您的评论

  select a.id, a.item 
    from items a, items_tags b, tags c 
   where a.id = b.item_id
     and b.tag_id = c.id
group by id, item
  having (group_concat(c.tag) like '%yellow%' 
         and  group_concat(c.tag) like '%fruit%')
      or group_concat(c.tag) = 'red';

此查询提供项目表中的ID和项目.它给出带有黄色和水果标签的物品.以及只有红色标签的物品.

This query gives id and item from items table. It gives item which has both yellow and fruit tag. and the items with only red tag.

如果要获取带有两个标签且只有两个标签的项目，请在具有条件子句中使用以下条件

if you want to get items with two tags and only two tags then use following condition in having clause

(group_concat(c.tag) like '%yellow%' 
and group_concat(c.tag) like '%fruit%'
and count(*) = 2) 

这篇关于MySQL查询来搜索带有特定标签的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！