表结构
oid node
G001 40
G001 41
G001 42
G002 40
G002 41
G003 52
G003 45我要取得node等于40 41 42的oid
这个表中应该取得G001
请教该语句怎么写
oid node
G001 40
G001 41
G001 42
G002 40
G002 41
G003 52
G003 45我要取得node等于40 41 42的oid
这个表中应该取得G001
请教该语句怎么写
from T
where node in(40,41,42)
group by oid
having count(distinct node)=3
FROM TB T1
INNER JOIN TB T2 ON T1.NODE=40 AND T2.NODE=41 AND T1.OID=T2.OID
INNER JOIN TB T3 ON T3.NODE=42 AND T1.OID=T3.OID
from T a
where node =40
and exists (
select 1 from T
where oid=a.oid and node = 41
)
and exists (
select 1 from T
where oid=a.oid and node = 42
)
select oid from tb where node in (40,41,42) group by oid
if exists(select * from sysobjects where [name]='tb')
drop table tb
go
create table tb
(
oid varchar(10),
node int
)
go
insert into tb(oid,node)
(
select 'G001',40 union
select 'G001',41 union
select 'G001',42 union
select 'G002',40 union
select 'G002',41 union
select 'G003',52 union
select 'G003',45
)
go
select oid from tb where node in(40,41,42) group by oid having count(distinct node)=3
go
结果为:
oid
----------
G001(1 行受影响)