--> 测试数据:@ta declare @ta table([c_ident] int,[t_ident] int) insert @ta select 12,33 union all select 3,34 union all select 2,36 --> 测试数据:@tb declare @tb table([c_ident] int,[t_ident] int,[num] int) insert @tb select 2,36,10 union all select 3,34,8 union all select 12,33,10 union all select 3,36,6 union all select 12,36,6 select * from @tb a where not exists(select 1 from @ta b where a.[c_ident]=b.[c_ident] and a.[t_ident]=b.[t_ident]) /* c_ident t_ident num ----------- ----------- ----------- 3 36 6 12 36 6(2 行受影响)*/
select b.* from Table2 b where not exists ( select a.* from Table1 a where a.c_ident = b.c_ident and a.t_ident = b.t_ident )
select * from tb2 as a where not exists(select * from tb1 where a.c_ident=c_ident and a.t_ident=t_ident)
select * from 表二 t where not exists(select 1 from 表一 where c_ident=t.c_ident and t_ident=t.t_ident)
楼上的方法都可以,我说另外一种方法吧 select a.* from tb2 as a left join tb1 as b on a.c_ident=b.c_ident and a.t_ident=b.t_ident where b.c_ident is null
declare @ta table([c_ident] int,[t_ident] int)
insert @ta
select 12,33 union all
select 3,34 union all
select 2,36
--> 测试数据:@tb
declare @tb table([c_ident] int,[t_ident] int,[num] int)
insert @tb
select 2,36,10 union all
select 3,34,8 union all
select 12,33,10 union all
select 3,36,6 union all
select 12,36,6
select * from @tb a where not exists(select 1 from @ta b where a.[c_ident]=b.[c_ident] and a.[t_ident]=b.[t_ident])
/*
c_ident t_ident num
----------- ----------- -----------
3 36 6
12 36 6(2 行受影响)*/
select b.* from Table2 b where not exists ( select a.* from Table1 a where a.c_ident = b.c_ident and a.t_ident = b.t_ident )
where not exists(select * from tb1
where a.c_ident=c_ident and a.t_ident=t_ident)
select a.*
from tb2 as a
left join tb1 as b
on a.c_ident=b.c_ident and a.t_ident=b.t_ident
where b.c_ident is null