表A ID ATTRID FILMID CONTENT
1 1 1 ASD
2 2 1 QWE
3 3 1 iew
4 1 2 ASD
5 2 2 SDF
6 3 2 dsf 我想在这个表中查询出 同时具有 asd 和sdf 的 filmid
查询结果应该是2 我该如何查询?
1 1 1 ASD
2 2 1 QWE
3 3 1 iew
4 1 2 ASD
5 2 2 SDF
6 3 2 dsf 我想在这个表中查询出 同时具有 asd 和sdf 的 filmid
查询结果应该是2 我该如何查询?
declare @tb table(ID int,ATTRID int, FILMID int,CONTENT varchar(20))
insert @tb
select 1, 1, 1, 'ASD' union all
select 2, 2, 1, 'QWE' union all
select 3, 3, 1, 'iew' union all
select 4, 1, 2, 'ASD' union all
select 5, 2, 2, 'SDF' union all
select 6, 3, 2, 'dsf'
select FILMID from @tb where CONTENT in('ASD','SDF')
group by FILMID
having count(FILMID)=2FILMID
-----------
2
再加一行数据测试一下
declare @tb table(ID int,ATTRID int, FILMID int,CONTENT varchar(20))
insert @tb
select 1, 1, 1, 'ASD' union all
select 2, 2, 1, 'QWE' union all
select 3, 3, 1, 'iew' union all
select 4, 1, 2, 'ASD' union all
select 5, 2, 2, 'SDF' union all
select 6, 3, 2, 'dsf' union all
select 7, 4, 1, 'SDF'select FILMID from @tb where CONTENT in('ASD','SDF')
group by FILMID
having count(FILMID)=2FILMID
-----------
1
2