A表结果: e f g ---------- ---------- ---------- 1 1 1 2 2 2 3 3 3 4 4 4 B表结果: e f g ---------- ---------- ---------- 1 1 1 想要的结果是:A表存在B表不存在的数据,即结果为: e f g ---------- ---------- ---------- 2 2 2 3 3 3 4 4 4
declare @tb table (e int,f int , g int) insert @tb select 1,1,1 union all select 2,2,2 union all select 3,3,3 union all select 4,4,4declare @tc table (e int,f int , g int) insert @tc select 1,1,1 select t.e,t.f,t.g from @tb t,(select * from @tc)tc where tc.e<>t.e and tc.f<>t.f and tc.g <> t.g
select t.e,t.f,t.g from @tb t,@tc tc where tc.e<>t.e and tc.f<>t.f and tc.g <> t.g
SELECT * FROM A WHERE CHECKSUM(*) NOT IN(SELECT CHECKSUM(*) FROM B)
insert @tb select
1,1,1 union all
select 2,2,2 union all
select 3,3,3 union all
select 4,4,4declare @tc table (e int,f int , g int)
insert @tc select
1,1,1 select t.e,t.f,t.g from @tb t,(select * from @tc)tc where
tc.e<>t.e and tc.f<>t.f and tc.g <> t.g
select t.e,t.f,t.g from @tb t,@tc tc where
tc.e<>t.e and tc.f<>t.f and tc.g <> t.g