SQL标签列表和标签过滤
我有一个 SQL 数据库,我在其中存储用户和与用户关联的标签(多对多关系).我有带有 users
表、tags
表和桥"表 usertag
的经典模式,它将用户与标签联系起来:
I have a SQL database in which I store users and tags associated to users (many to many relationship). I have the classic schema with users
table, tags
table and the "bridge" table usertag
which links users with tags:
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()
函数进行查询,以逗号分隔字段的形式检索所有用户及其标签:
Now, I made a query to retrieve all the users and their tags as a comma separated field, using the GROUP_CONCAT()
function:
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 运算符.
The problem is that now I want to implement tag filtering on top of that, i.e. being able to query the users by tag (or multiple tags). The filter should work using the AND operator.
例如:获取标签为 Red (10) AND Green (20) 的用户:
For example: Get users with tag Red (10) AND Green (20):
output:
+---------+---------+----------+
| Id | Name | Tags |
+---------+---------+----------+
| 2 | Bob | 10,20 |
| 4 | David | 10,30,20 |
+---------+---------+----------+
另一个例子:获取标签为红色(10)的用户:
Another example: Get users with tag Red (10):
output:
+---------+---------+----------+
| Id | Name | Tags |
+---------+---------+----------+
| 2 | Bob | 10,20 |
| 4 | David | 10,30,20 |
| 5 | Eve | 10 |
+---------+---------+----------+
另一个例子:获取标签为红色(10)、绿色(20)和蓝色(30)的用户:
Another example: Get users with tag Red (10), Green (20) and Blue (30):
output:
+---------+---------+----------+
| Id | Name | Tags |
+---------+---------+----------+
| 4 | David | 10,30,20 |
+---------+---------+----------+
如何实现这样的查询?这个问题在 SO 上非常相似,它确实有效,但它不处理 GROUP_CONCAT()
字段,这是我想保持原样
How can I implement such query? This question on SO is very similar and it actually works but it doesn't deal with the GROUP_CONCAT()
field which is something I'd like to keep as it is
这里是 SQL 小提琴 http://sqlfiddle.com/#!9/291a5c/8
Here the SQL fiddle http://sqlfiddle.com/#!9/291a5c/8
编辑
可以想象这个查询有效:
One may imagine that this query works:
检索所有标记为红色 (10) 和蓝色 (20) 的用户:
Retrieve all users with tag Red (10) and 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!
which username-wise is correct (Bob and David) but the Tags
field is missing the tag 30 from David's list!
推荐答案
left join
tags
表并在 join 子句中包含正在搜索的 id 并检查拥有
.
left join
the tags
table and include the id's being searched for in the join clause and check for counts in 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
.例如,
One more option is to use FIND_IN_SET
if there are limited values. For example,
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
相关文章