具有m:n关系的SQL SELECT [英] SQL SELECT with m:n relationship

问题描述

我与用户和标签之间存在m:n关系.一个用户可以拥有m个标签，而一个标签可以属于n个用户.表格看起来像这样:

I have m:n relationship between users and tags. One user can have m tags, and one tag can belong to n users. Tables look something like this:

USER:
ID
USER_NAME

USER_HAS_TAG:
USER_ID
TAG_ID

TAG:
ID
TAG_NAME

比方说，我需要选择所有具有标签"apple"，"orange"和"banana"的用户.使用SQL(MySQL DB)完成此操作的最有效方法是什么?

Let's say that I need to select all users, who have tags "apple", "orange" AND "banana". What would be the most effective way to accomplish this using SQL (MySQL DB)?

推荐答案

除了其他好的答案之外，还可以在WHERE子句中检查条件:

In addition to the other good answers, it's also possible to check the condition in a WHERE clause:

select *
from user u
where 3 = (
    select count(distinct t.id)
    from user_has_tag uht
    inner join tag t on t.id = uht.tag_id
    where t.name in ('apple', 'orange', 'banana') 
    and uht.user_id = u.userid
)

count(distinct ...)确保即使用户具有多个香蕉"标签，标签也仅被计数一次.

The count(distinct ...) makes sure a tag is counted only once, even if the user has multiple 'banana' tags.

顺便说一下，该网站fruitoverflow.com尚未注册:)

By the way, the site fruitoverflow.com is not yet registered :)

这篇关于具有m:n关系的SQL SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！