sqlserver 中间表查询

[illhf2008]

怎样查询ImgTag_Id=1 and ImgTag_Id=2的MyMaterial_Id数据，我想要的查询结果为23,24
sql语句怎么写啊？

[發糞塗牆]

select distinct mymaterial_id from tab where ImgTag_Id in (1,2)???

[發糞塗牆]

有其他规则不？

[illhf2008]

引用 1 楼 DBA_Huangzj 的回复:

select distinct mymaterial_id from tab where ImgTag_Id in (1,2)???

那样 对应的25的记录也会查出来

[illhf2008]

引用 2 楼 DBA_Huangzj 的回复:

有其他规则不？

没有其他的规则 就是查询ImgTag_Id=1 和ImgTag_Id=2的交集

[發糞塗牆]

select [mymaterial_id] from [huang]a WHERE imgtag_id=1 INTERSECT select [mymaterial_id] from [huang]a WHERE imgtag_id=2

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-10-21 13:06:01
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([imgtag_id] int,[mymaterial_id] int)
insert [huang]
select 1,23 union all
select 1,24 union all
select 1,25 union all
select 2,23 union all
select 2,24
--------------开始查询--------------------------

select [mymaterial_id] from [huang]a
WHERE imgtag_id=1
INTERSECT 
select [mymaterial_id] from [huang]a
WHERE imgtag_id=2
----------------结果----------------------------
/* 
mymaterial_id
-------------
23
24
*/

[Andy__Huang]

select mymaterial_id 
from tab 
where ImgTag_Id in (1,2)
group by mymaterial_id
having COUNT(*)>1

[Andy__Huang]

create table tab([imgtag_id] int,[mymaterial_id] int)
insert tab
select 1,23 union all
select 1,24 union all
select 1,25 union all
select 2,23 union all
select 2,24


select mymaterial_id 
from tab 
where ImgTag_Id in (1,2)
group by mymaterial_id
having COUNT(*)>1

/*
mymaterial_id
23
24
*/

[阳泉酒家小当家]

DROP TABLE T

create table t(imgtag_id int,mymaterial_id int)

insert into t
select 1,23 union all
select 1,24 union all
select 1,25 union all
select 2,23 union all
select 2,24 

select t1.mymaterial_id
from T T1
INNER JOIN t t2
        on t1.mymaterial_id = t2.mymaterial_id
where t1.imgtag_id = 2      
      and t2.imgtag_id = 1
/*
mymaterial_id
23
24
*/      

[illhf2008]

谢谢两位大神！

[阳泉酒家小当家]

上面写的不好，修改了：

DROP TABLE T

create table t(imgtag_id int,mymaterial_id int)

insert into t
select 1,23 union all
select 1,24 union all
select 1,25 union all
select 2,23 union all
select 2,24 

select mymaterial_id
from T 
where imgtag_id (1,2)
group by mymaterial_id
having count(*) >= 2  --只有出现2次的，才返回，25只出现一次，所以不会返回
/*
mymaterial_id
23
24
*/      

[火拼阿三]

是这样吗？

DROP TABLE #tab
create table #tab([imgtag_id] int,[mymaterial_id] int)
insert #tab
select 1,23 union all
select 1,24 union all
select 1,25 union all
select 2,23 union all
select 2,24

select * from #tab


select b.* from (
select [mymaterial_id] from #tab  where [imgtag_id]in (1,2)
group by [mymaterial_id] having COUNT(1)>1) a
left join #tab b
on a.mymaterial_id=b.mymaterial_id
order by imgtag_id