mysql - 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 |
    +---------+---------+----------+

如何实现这样的查询？ This question在 SO 上非常相似，它确实有效，但它不处理 GROUP_CONCAT() 字段，这是我想保持原样的东西

这里是 SQL fiddle http://sqlfiddle.com/#!9/291a5c/8

编辑

可以想象这个查询有效:

检索所有带有标签 Red (10) 和 Blue (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