Select Distinct A.a From A Where Exists (Select * From A t1 Where A.a = t1.a and not Exists (Select * from B Where a = A.b and b = t1.c))
select f1.a from f1 right outer join b1 on f1.b=b1.b and f1.c=b1.c group by f1.a having count(*)>((select count(*) from b1)-1)
--建立測試環境 create table d(a varchar(50),b varchar(50),c varchar(50)) insert into d select 'e1', 'f1', 'g1' union all select 'e1' , 'f2' , 'g2' union all select 'e2', 'f1' , 'g1' union all select 'e3', 'f2', 'g2'create table d1(a varchar(50),b varchar(50)) insert into d1 select 'f1' , 'g1' union all select 'f2' , 'g2'--測試 select identity(int,1,1) ID ,d.a into #t from d , d1 where d.b=d1.a and d.c=d1.b select A.a from #t A,#t B where a.a=b.a and a.id>b.id --刪除測試用例 drop table d drop table d1 drop table #t
Where Exists
(Select * From A t1
Where A.a = t1.a
and not Exists
(Select * from B
Where a = A.b
and b = t1.c))
select f1.a
from f1 right outer join b1 on f1.b=b1.b and f1.c=b1.c
group by f1.a
having count(*)>((select count(*) from b1)-1)
create table d(a varchar(50),b varchar(50),c varchar(50))
insert into d
select 'e1', 'f1', 'g1' union all
select 'e1' , 'f2' , 'g2' union all
select 'e2', 'f1' , 'g1' union all
select 'e3', 'f2', 'g2'create table d1(a varchar(50),b varchar(50))
insert into d1
select 'f1' , 'g1' union all
select 'f2' , 'g2'--測試
select identity(int,1,1) ID ,d.a into #t from d , d1 where d.b=d1.a and d.c=d1.b
select A.a from #t A,#t B where a.a=b.a and a.id>b.id
--刪除測試用例
drop table d
drop table d1
drop table #t