看看下面的例子: create schema authorization sa create table #t1 ( i1 int , c1 char( 1 ) , vc1 varchar( 10 ) ) create table #t2 ( i2 int , c2 char( 1 ) , vc2 varchar( 10 ) ) goinsert into #t1 values( 1 , '1' , '1111' ); insert into #t1 values( 11 , 'a' , 'aaaa' ); insert into #t2 values( 1 , '1' , '1111' ); insert into #t2 values( 2 , '2' , '2222' ); go -- Orphans in #t1 only select i1 , c1 , vc1 from ( select * , checksum( * ) as chksum1 from #t1 ) as t1 where not exists ( select * from ( select * , checksum( * ) as chksum2 from #t2 ) as t2 where chksum1 = chksum2 );-- Orphans in #t2 only select i2 , c2 , vc2 from ( select * , checksum( * ) as chksum2 from #t2 ) as t2 where not exists ( select * from ( select * , checksum( * ) as chksum1 from #t1 ) as t1 where chksum1 = chksum2 ) -- Matching rows in #t1 & #t2 select i1 , c1 , vc1 from ( select * , checksum( * ) as chksum1 from #t1 ) as t1 where exists ( select * from ( select * , checksum( * ) as chksum2 from #t2 ) as t2 where chksum1 = chksum2 ); go-- You can simplify this further if you define a computed column for -- the checksum like: alter table #t1 Add chksum as ( checksum( i1 , c1 , vc1 ) ); alter table #t2 Add chksum as ( checksum( i2 , c2 , vc2 ) ); go -- Orphans in #t1 only select i1 , c1 , vc1 from #t1 as t1 where not exists ( select * from #t2 as t2 where t1.chksum = t2.chksum );-- Orphans in #t2 only select i2 , c2 , vc2 from #t2 as t2 where not exists ( select * from #t1 as t1 where t1.chksum = t2.chksum ) -- Matching rows in #t1 & #t2 select i1 , c1 , vc1 from #t1 as t1 where exists ( select * from #t2 as t2 where t1.chksum = t2.chksum ); go drop table #t2; drop table #t1; go
create schema authorization sa
create table #t1 (
i1 int , c1 char( 1 ) , vc1 varchar( 10 )
)
create table #t2 (
i2 int , c2 char( 1 ) , vc2 varchar( 10 )
)
goinsert into #t1 values( 1 , '1' , '1111' );
insert into #t1 values( 11 , 'a' , 'aaaa' );
insert into #t2 values( 1 , '1' , '1111' );
insert into #t2 values( 2 , '2' , '2222' );
go
-- Orphans in #t1 only
select i1 , c1 , vc1 from
(
select * , checksum( * ) as chksum1 from #t1
) as t1
where not exists ( select * from (
select * , checksum( * ) as chksum2 from #t2
) as t2
where chksum1 = chksum2
);-- Orphans in #t2 only
select i2 , c2 , vc2 from
(
select * , checksum( * ) as chksum2 from #t2
) as t2
where not exists ( select * from (
select * , checksum( * ) as chksum1 from #t1
) as t1
where chksum1 = chksum2
)
-- Matching rows in #t1 & #t2
select i1 , c1 , vc1 from
(
select * , checksum( * ) as chksum1 from #t1
) as t1
where exists ( select * from (
select * , checksum( * ) as chksum2 from #t2
) as t2
where chksum1 = chksum2
);
go-- You can simplify this further if you define a computed column for
-- the checksum like:
alter table #t1 Add chksum as ( checksum( i1 , c1 , vc1 ) );
alter table #t2 Add chksum as ( checksum( i2 , c2 , vc2 ) );
go
-- Orphans in #t1 only
select i1 , c1 , vc1 from #t1 as t1
where not exists ( select * from #t2 as t2
where t1.chksum = t2.chksum
);-- Orphans in #t2 only
select i2 , c2 , vc2 from #t2 as t2
where not exists ( select * from #t1 as t1
where t1.chksum = t2.chksum
)
-- Matching rows in #t1 & #t2
select i1 , c1 , vc1 from #t1 as t1
where exists ( select * from #t2 as t2
where t1.chksum = t2.chksum
);
go
drop table #t2;
drop table #t1;
go