A表(acode,Aname) B表(Acode,Bname) c表(Bname,CcreateDate)
A表与B表是1对多的关系。C表是对B表的统计。
现在 想这样。
要查询A表的记录,条件是A表所关联的B表里面的Bname 要全部在C表的Bname里面
A表与B表是1对多的关系。C表是对B表的统计。
现在 想这样。
要查询A表的记录,条件是A表所关联的B表里面的Bname 要全部在C表的Bname里面
select * from A表 a
left join B表 b on (a.Acode=b.Acode)
left join C表 c on (b.Bname=c.Bname)
b表的bname要全部在c表里面 的a 的记录才查出来
from A
where not exists(select 1 from B where ACode=A.ACode and not exists(select 1 from C where BName=B.Name))
go
-----苦苦的潜行者
create table a(acode int,aname varchar(10))
create table b(acode int,bname varchar(10))
create table c(bname varchar(10),createdate datetime)
insert a
select 1,'dsads' union
select 2,'dsadsadsad'
insert b
select 1,'ui' union all
select 1,'kl' union all
select 2,'jk'
insert c
select 'ui','2012-01-01' union all
select 'dsa','2012-01-01' union all
select 'kl','2012-01-01'
go
select bname from b
join a on a.acode=b.acode
where b.bname in (select bname from c)
--这两个where选一个,都行
--where exists (select 1 from c where b.bname=bname)--或select bname from a,b
where a.acode=b.acode
--and b.bname in (select bname from c)
and exists (select 1 from c where b.bname=bname)
/*
(所影响的行数为 2 行)
bname
-----
ui
kl*/
go
drop table a,b,c
select a.* from a,b
where a.acode=b.acode
and b.bname in (select bname from c)
and not exists (select 1 from b where bname not in (select bname from c))