select [图片编号],[图片标签ID] from (select [图片编号],[图片标签ID] from(select [图片编号],[图片标签ID] from [表名] where [图片标签ID] =2) where [图片标签ID]=3) where [图片标签ID]=4 and [图片标签ID] in(2,3,4)
select [图片编号],[图片标签ID] from (select [图片编号],[图片标签ID] from(select [图片编号],[图片标签ID] from [表名] where [图片标签ID] =2) where [图片标签ID]=3) where [图片标签ID]=4 and [图片标签ID] in(2,3,4)
感谢,楼上兄弟写的,不过我看了一下似乎还是不对的,子查询最里面的把 [图片标签ID] =2 做为条件,出来一层后又把 [图片标签ID]= 3 做为条件, 肯定不行的,这样到最后一条记录也没查到。我的思路是这样的select [图片编号], count(*) from [表名] where [图片标签ID] in(1,5) group by pictureattributemapping_pictureitem having count(*) >= 21,5其实是我从另外的表里通过像似搜索查出来的,2代表查出来有两条记录。但是总觉的这样写不好,我用的是SQL SERVER 2005 请高教高手好的写法 。
declare @t table(picID int, LabelID int) insert into @t select 1, 3 union select 1, 4 union select 1, 5 union select 2, 1 union select 2, 3 union select 2, 7 union select 3, 1 union select 3, 5 union select 3, 7 union select 3, 4select a.* from (select * from @t where LabelID = 1) as a inner join (select picID from @t where LabelID = 5) as b on a.picID = b.picID(10 行受影响) picID LabelID ----------- ----------- 3 1(1 行受影响)
图片ID 标签ID
1 3
1 4
1 5
2 1
2 3
2 7
3 1
3 5
3 7
3 4
如果我有标签号为 1,5 那么应该返回的图片ID是 3因为他含有1,5
谢谢
group by pictureattributemapping_pictureitem having count(*) >= 21,5其实是我从另外的表里通过像似搜索查出来的,2代表查出来有两条记录。但是总觉的这样写不好,我用的是SQL SERVER 2005 请高教高手好的写法 。
insert into @t select 1, 3
union select 1, 4
union select 1, 5
union select 2, 1
union select 2, 3
union select 2, 7
union select 3, 1
union select 3, 5
union select 3, 7
union select 3, 4select a.* from (select * from @t where LabelID = 1) as a
inner join (select picID from @t where LabelID = 5) as b on a.picID = b.picID(10 行受影响)
picID LabelID
----------- -----------
3 1(1 行受影响)