SQL查询查找包含多个标签的资源

发布于 2024-11-03 00:42:18 字数 488 浏览 0 评论 0原文

我有两张桌子。

Resource:
md5 (PK), link, title

Tags:
md5 (FK), category

它是一对多的关系，因此一个资源可以有多个标签。我希望能够提取具有两个标签的资源，例如包含标签“web”和“blog”的资源。如果我使用“OR”，它显然会返回仅包含“web”或仅包含“blog”的文档，但如果我使用AND，即使我知道有包含这两个标签的资源，我也不会得到任何结果

SELECT DISTINCT tags.MD5, Resource.Title, Resource.Link, tags.Category
FROM Resource
INNER JOIN tags ON Resource.MD5 = tags.MD5
WHERE       
    (tags.Category = @tag)
OR
    (tags.Category = @tag2)
ORDER BY tags.MD5

原文

I have two tables.

Resource:
md5 (PK), link, title

Tags:
md5 (FK), category

it is a one to many relationship such that a resource can have a number of tags.
I want to be able to extract a resource that has both tags, for e.g. a resource that contains the tag 'web' and 'blog' in it. If I use 'OR', it will obviously return even document that contain only 'web' or only contain 'blog', but if I use AND, I get no results even though I know there are resources that contain both tags

SELECT DISTINCT tags.MD5, Resource.Title, Resource.Link, tags.Category
FROM Resource
INNER JOIN tags ON Resource.MD5 = tags.MD5
WHERE       
    (tags.Category = @tag)
OR
    (tags.Category = @tag2)
ORDER BY tags.MD5

分享到QQ

分享到微博

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

发布评论

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

魂牵梦绕锁你心扉 2024-11-10 00:42:19

你想要这样做：

select t.MD5, r.Title, r.Link, t.Category
from Resource r inner join Tags t on r.MD5 = t.MD5
where exists (select category from Tags t1 where category = @tag and t1.MD5 = r.MD5)
and exists (select category from Tags t1 where category = @tag2 and t1.MD5 = r.MD5)

You want to do like that:

select t.MD5, r.Title, r.Link, t.Category
from Resource r inner join Tags t on r.MD5 = t.MD5
where exists (select category from Tags t1 where category = @tag and t1.MD5 = r.MD5)
and exists (select category from Tags t1 where category = @tag2 and t1.MD5 = r.MD5)

回复收藏 0 原文

岁月静好 2024-11-10 00:42:19

以下将过滤掉不具有这两个标签的资源：

SELECT DISTINCT Resource.MD5, Resource.Title, Resource.Link, t1.Category, t2.Category 
FROM Resource 
INNER JOIN tags t1 ON Resource.MD5 = t1.MD5 AND t1.category = @tag
INNER JOIN tags t2 ON Resource.MD5 = t2.MD5 AND t2.category = @tag2

The following will filter out resources that don't possess both tags:

SELECT DISTINCT Resource.MD5, Resource.Title, Resource.Link, t1.Category, t2.Category 
FROM Resource 
INNER JOIN tags t1 ON Resource.MD5 = t1.MD5 AND t1.category = @tag
INNER JOIN tags t2 ON Resource.MD5 = t2.MD5 AND t2.category = @tag2

回复收藏 0 原文

憧憬巴黎街头的黎明 2024-11-10 00:42:19

select r.*
from resource as r
left join tags as t
on r.md5 = t.md5
where t.category in ('web','blog')
group by r.md5
having count(distinct(t.category)) = 2

select r.*
from resource as r
left join tags as t
on r.md5 = t.md5
where t.category in ('web','blog')
group by r.md5
having count(distinct(t.category)) = 2

回复收藏 0 原文

~没有更多了~