这个SQL查询有什么问题？
这个SQL查询有什么问题？
提问于 2010-05-18 13:58:09
我有两张表:照片和photograph_tags。Photograph_tags包含一个名为photograph_id (照片中的id)的列。一张照片可以有多个标签。我有一个与三个标签相关的照片行:男孩、小溪和水。但是，运行以下查询将返回0行
SELECT p.*
FROM photographs p, photograph_tags c
WHERE c.photograph_id = p.id
AND (c.value IN ('dog', 'water', 'stream'))
GROUP BY p.id
HAVING COUNT( p.id )=3这个查询有什么问题吗？
My tables look like so
-----------------------
photographs
-----------------------
id | title | location
------------------------
7 | asdf | c:\...
-----------------------
photograph_tags
-----------------------
id | photograph_id | value
1 | 7 | dog
2 | 7 | water
3 | 7 | stream
4 | 7 | mountains
I want to pull all photograph rows that relate to at least all three of the searched tags回答 3
Stack Overflow用户
回答已采纳
发布于 2010-05-18 14:04:56
获取具有您指定的3个标签(或更多)的所有照片。从标签开始，加入照片。
select
p.id
from photographs p
left join photograph_tags c
on p.id = c.photograph_id
and c.value IN ('dog', 'water', 'stream')
group by p.id
having count(c.value) >= 3测试上面的代码：
create table #photograph_tags (
photograph_id INT,
value varchar(50)
)
create table #photographs (
id int
)
insert into #photographs values (7)
insert into #photographs values (8)
insert into #photograph_tags values (7, 'dog')
insert into #photograph_tags values (7, 'water')
insert into #photograph_tags values (7, 'stream')
insert into #photograph_tags values (7, 'mountains')
insert into #photograph_tags values (8, 'stream')
insert into #photograph_tags values (8, 'mountains')
select * from #photographs
select * from #photograph_tags
select
p.id
from #photographs p
left join #photograph_tags c
on p.id = c.photograph_id
and c.value IN ('dog', 'water', 'stream')
group by p.id
having count(c.value) >= 3
drop table #photograph_tags
drop table #photographsStack Overflow用户
发布于 2010-05-18 14:46:55
SELECT p.* FROM photographs p join
(select id, COUNT(id) as TagCount
FROM Photograph_Tags c
WHERE c.value IN ('dog', 'water', 'stream')
group by id)
as TagCounts on p.id = TagCounts.id
WHERE TagCount = 3Stack Overflow用户
发布于 2010-05-18 15:08:59
SELECT p.* FROM photographs p WHERE (c.value IN (‘狗’，‘水’，'stream')) AND (SELECT COUNT(*) FROM photograph_tags c
WHERE c.photograph\_id = p.id) >= 3; 将为您提供至少具有三个标签的照片。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2854830
复制相关文章
相似问题
腾讯云开发者
