MYSQL仅在集合中查询

发布于 2024-12-07 16:42:56 字数 442 浏览 0 评论 0原文

我有一个表，它将条目与关联标签链接起来，其中包含以下数据：

entry_id |  tag_id
1        |  1
2        |  1
3        |  1
1        |  2
2        |  2

我正在尝试编写一个查询，该查询仅返回标记为 1 AND 2 的条目，在此示例中，将返回条目 1 和 2，而不会返回条目 3，因为它没有这两个标签。我正在使用的当前查询有效，但我知道不可能是正确的：

SELECT entry_id, GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id)
FROM tags
GROUP BY entry_id
HAVING GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id) LIKE "%1,2%";

原文

I have a table that links entries with associated tags with the following data in it:

entry_id |  tag_id
1        |  1
2        |  1
3        |  1
1        |  2
2        |  2

I am trying to write a query that returns only entries tagged with 1 AND 2, in this example entries 1 and 2 would be returned, while 3 would not, because it does not have both tags. The current query I am using works but I know can't be right:

SELECT entry_id, GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id)
FROM tags
GROUP BY entry_id
HAVING GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id) LIKE "%1,2%";

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜心小果奶 2024-12-14 16:42:56

如果 (entry_id, tag_id) 是唯一的：

SELECT entry_id
FROM yourtable
WHERE tag_id IN (1, 2)
GROUP BY entry_id
HAVING COUNT(*) = 2

另一种方法不需要唯一性，而且速度更快：

SELECT T1.entry_id
FROM yourtable T1
JOIN yourtable T2
ON T1.entry_id = T2.entry_id
AND T1.tag_id = 1
WHERE T2.tag_id = 2

If (entry_id, tag_id) is unique:

SELECT entry_id
FROM yourtable
WHERE tag_id IN (1, 2)
GROUP BY entry_id
HAVING COUNT(*) = 2

An alternative approach that doesn't require uniqueness and can also be faster:

SELECT T1.entry_id
FROM yourtable T1
JOIN yourtable T2
ON T1.entry_id = T2.entry_id
AND T1.tag_id = 1
WHERE T2.tag_id = 2

回复收藏 0 原文

北恋 2024-12-14 16:42:56

SELECT entry_id
FROM tags t1 inner join tags t2 
on (t1.entry_id = t2.entry_id and t1.tag_id = 1 and t2.tag_id = 2)

SELECT entry_id
FROM tags t1 inner join tags t2 
on (t1.entry_id = t2.entry_id and t1.tag_id = 1 and t2.tag_id = 2)

回复收藏 0 原文

甩你一脸翔 2024-12-14 16:42:56

这是一个自我加入的好例子......

SELECT tagged1.entry_id
FROM tags tagged1
JOIN tags tagged2
    ON tagged1.entry_id = tagged2.entry_id
        AND tagged1.tag_id = 1
        AND tagged2.tag_id = 2;

This is a good case for a self-join...

SELECT tagged1.entry_id
FROM tags tagged1
JOIN tags tagged2
    ON tagged1.entry_id = tagged2.entry_id
        AND tagged1.tag_id = 1
        AND tagged2.tag_id = 2;

回复收藏 0 原文

~没有更多了~