【www.bbyears.com--班主任工作总结】
代码如下
use tempdb
go
if object_id("table_left") is not null drop table table_left
if object_id("table_right") is not null drop table table_right
go
create table table_left(groupId nvarchar(5),dataSub1 nvarchar(10),dataSub2 nvarchar(10))
create table table_right(groupId nvarchar(5),dataSub1 nvarchar(10),dataSub2 nvarchar(10))
go
alter table table_left add dataChecksum as checksum(dataSub1,dataSub2)
alter table table_right add dataChecksum as checksum(dataSub1,dataSub2)
go
create nonclustered index ix_table_left_cs on table_left(dataChecksum)
create nonclustered index table_right_cs on table_right(dataChecksum)
go
set nocount on
go
insert into table_right(groupId,dataSub1,dataSub2)
select "#1","data1","data7" union all
select "#1","data2","data8" union all
select "#1","data3","data9" union all
select "#2","data55","data4" union all
select "#2","data55","data5"
insert into table_left(groupId,dataSub1,dataSub2)
select "#11","data1","data7" union all
select "#11","data2","data8" union all
select "#11","data3","data9" union all
select "#22","data55","data0" union all
select "#22","data57","data2" union all
select "#33","data99","data4" union all
select "#33","data99","data6"
go
--select
select distinct a.groupId
from table_left a
inner join table_right b on b.dataChecksum=a.dataChecksum
and b.dataSub1=a.dataSub1
and b.dataSub2=a.dataSub2
where not exists(select x.dataSub1,x.dataSub2 from table_left x where x.groupId=a.groupId except select y.dataSub1,y.dataSub2 from table_right y where y.groupId=b.groupId )
and not exists(select x.dataSub1,x.dataSub2 from table_right x where x.groupId=b.groupId except select y.dataSub1,y.dataSub2 from table_left y where y.groupId=a.groupId )