SQL标记列表和标记过滤

Question

我有一个SQL数据库，用于存储用户和与用户相关联的标签（许多关系）。我有带有users表，tags表和“桥”表的经典架构，该表usertag将用户与标签链接在一起：

users table:
    +---------+---------+
    | Id      |  Name   |
    +---------+---------+
    | 1       | Alice   |
    | 2       | Bob     |
    | 3       | Carl    |
    | 4       | David   |
    | 5       | Eve     |
    +---------+---------+

tags table:
    +---------+---------+
    | Id      | Name    |
    +---------+---------+
    | 10      | Red     |
    | 20      | Green   |
    | 30      | Blue    |
    +---------+---------+

usertag table:
    +---------+---------+
    | UserId  |  TagId  |
    +---------+---------+
    | 2       | 10      |
    | 2       | 20      |
    | 1       | 30      |
    | 4       | 20      |
    | 4       | 10      |
    | 4       | 30      |
    | 5       | 10      |
    +---------+---------+

现在，我使用以下GROUP_CONCAT()函数进行查询以逗号分隔的字段的形式检索所有用户及其标签：

SELECT u.*, GROUP_CONCAT(ut.tagid) as tags FROM users as u LEFT JOIN usertag as ut ON u.id = ut.userid GROUP BY u.id

这给了我正确的输出：

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 1       | Alice   | 30       |
    | 2       | Bob     | 10,20    |
    | 3       | Carl    | (null)   |
    | 4       | David   | 10,30,20 |
    | 5       | Eve     | 10       |
    +---------+---------+----------+

问题是，现在我想在此基础上实现标签过滤，即能够按标签（或多个标签）查询用户。过滤器应使用AND运算符工作。

例如：获得标记为红色（10）和绿色（20）的用户：

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 2       | Bob     | 10,20    |
    | 4       | David   | 10,30,20 |
    +---------+---------+----------+

另一个示例：使用标签Red（10）获取用户：

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 2       | Bob     | 10,20    |
    | 4       | David   | 10,30,20 |
    | 5       | Eve     | 10       |
    +---------+---------+----------+

另一个示例：使用标签为红色（10），绿色（20）和蓝色（30）的用户：

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 4       | David   | 10,30,20 |
    +---------+---------+----------+

如何实现这样的查询？关于SO的这个问题非常相似，并且确实有效，但是它不涉及该GROUP_CONCAT()领域，我想保留原样。

这里的SQL提琴http://sqlfiddle.com/#!9/291a5c/8

编辑

可能有人认为此查询有效：

检索带有标签红色（10）和蓝色（20）的所有用户：

 SELECT u.name, GROUP_CONCAT(ut.tagid)
    FROM users as u
    JOIN usertag as ut ON u.id = ut.userid
   WHERE ut.tagid IN (10,20)
GROUP BY u.id
  HAVING COUNT(DISTINCT ut.tagid) = 2

这使：

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 2       | Bob     | 10,20    |
    | 4       | David   | 10,20    |
    +---------+---------+----------+

哪个用户名明智（Bob和David）是正确的，但是该Tags字段缺少David列表中的标签30！

Answer 1

left join该tags表，并在join子句中包含要搜索的ID，并在中检查计数having。

SELECT u.id,u.name,GROUP_CONCAT(ut.tagid) as tags
FROM users u 
LEFT JOIN usertag as ut ON u.id = ut.userid 
LEFT JOIN tags t ON t.id=ut.tagid AND t.ID IN (10,20,30) --change this as needed
GROUP BY u.id,u.name
HAVING COUNT(ut.tagid) >= COUNT(t.id) AND COUNT(t.id) = 3 --change this number to the number of tags

FIND_IN_SET如果值有限，则可以使用另一种选择。例如，

SELECT * FROM (
SELECT u.*, GROUP_CONCAT(ut.tagid) as tags 
FROM users as u 
LEFT JOIN usertag as ut ON u.id = ut.userid 
GROUP BY u.id
) T
WHERE FIND_IN_SET('10',tags) > 0 AND FIND_IN_SET('20',tags) > 0