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 的记录才查出来
select * from A where not exists(select 1 from B where ACode=A.ACode and not exists(select 1 from C where BName=B.Name))
use test 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))
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))