js06金沙官网登录-js06.com-欢迎您

来自 计算机 2020-03-13 22:03 的文章
当前位置: js06金沙官网登录-js06.com-欢迎您 > 计算机 > 正文

根据SQL Server中如何比很多个表的各组数据 图阐述

开始

前一阵子,在品种中境遇那样叁个SQL查询需求,有三个相符结构的表(table_left & table_right),如下:

图1.

检查表table_left的各组(groupId卡塔尔(قطر‎,是不是在表table_right中存在有一组(groupIdState of Qatar数据(data卡塔尔(قطر‎与它的数码(data卡塔尔国完全相等.

如图1. 能够看看表table_left和table_right存在两组数据完整相等:

图2.

分析

从地方的八个表,能够清楚它们寄放的是一组一组的多寡;那么,接下去自个儿依靠数学集结的列举法和平运动算进行分析。

先经过集聚的列举法描述三个表的各组数据:

图3.

此处唯有三种景况,相等和不等于。对于不对等,可再分为部分相等、满含、和完全不等于。使用集结描述,可接收交集,子集,并集。如上面图4.,笔者列举出这三种数以万计的意况:

图4.

实现

在数据库中,要寻觅表table_left和表table_right存在相仿数量的组,方法比很多,这里作者列出二种常用的方式。

(下边包车型地铁SQL脚本,是以图4.的数额为根基参考卡塔尔(قطر‎

方法1:

金沙官网登录,因此"Select … From …Order by … xml for path(''卡塔尔(قطر‎"把各组的data列数据种类起来,别的分组以此格局达成data列数据连串起来;然后通过相比较两表的种类后字段是还是不是留存特出,即使相等就认证那正如多两组数据相等,由此可以肯定出表table_left的哪组数据在表table_right存在与它多少完全相等的组。

本着方法1,要求对原表扩展二个字段dataPath,用于存款和储蓄data列数据连串的结果,如:

复制代码 代码如下:alter table table_left add dataPath nvarchar(200) alter table table_right add dataPath nvarchar(200)

分组连串data列数据并update至刚新添的列dataPath,如:

复制代码 代码如下:update a set dataPath=b.dataPath from table_left a cross apply(select (select '-'+x.data from table_left x where x.groupId=a.groupId order by x.data for xml path('')) as dataPath)b update a set dataPath=b.dataPath from table_right a cross apply(select (select '-'+x.data from table_right x where x.groupId=a.groupId order by x.data for xml path('')) as dataPath)b

接下去正是查询了,如:

复制代码 代码如下:select distinct a.groupId from table_left a where exists(select 1 from table_right x where x.dataPath=a.dataPath)

完全代码:

复制代码 代码如下:View Code use tempdbgoif object_id('table_left') is not null drop table table_leftif object_id('table_right') is not null drop table table_rightgocreate table table_left(groupId nvarchar(5),data nvarchar(10))create table table_right(groupId nvarchar(5),data nvarchar(10))goalter table table_left add dataPath nvarchar(200)alter table table_right add dataPath nvarchar(200)gocreate nonclustered index ix_left on table_left(dataPath)create nonclustered index ix_right on table_right(dataPath)goset nocount ongoinsert into table_right(groupId,data)select '#1','data1' union allselect '#1','data2' union allselect '#1','data3' union allselect '#2','data55' union allselect '#2','data55' union allselect '#3','data91' union allselect '#3','data92' union allselect '#4','data65' union allselect '#4','data66' union allselect '#4','data67' union allselect '#4','data68' union allselect '#4','data69' union allselect '#5','data77' union allselect '#5','data79'insert into table_left(groupId,data)select '#11','data1' union allselect '#11','data2' union allselect '#11','data3' union allselect '#22','data55' union allselect '#22','data57' union allselect '#33','data99' union allselect '#33','data99' union allselect '#44','data66' union allselect '#44','data68' union allselect '#55','data77' union allselect '#55','data78' union allselect '#55','data79'goupdate a set dataPath=b.dataPath from table_left a cross apply(select (select '-'+x.data from table_left x where x.groupId=a.groupId order by x.data for xml path('')) as dataPath)bupdate a set dataPath=b.dataPath from table_right a cross apply(select (select '-'+x.data from table_right x where x.groupId=a.groupId order by x.data for xml path('')) as dataPath)b--select distinct a.groupId from table_left a where exists(select 1 from table_right x where x.dataPath=a.dataPath)

方法2:

通过SQL Sever提供的集运算符"Except",剖断两组非重复的多少。若是两组针对对方都一纸空文非重复的多寡,就声明这两组数据完全相等。如,表table_left中的组#11和表 table_right中的组#1,对列data进行"Except"集运算,无任是(#11 à #1)进行Except集运算,还是(#1 à #11 State of Qatar举办Except集结运算,都回到空结果,这就说明组#1 和#11的data数据完全相等,如:

复制代码 代码如下:select data from table_left where groupId='#11' except select data from table_right where groupId='#1' select data from table_right where groupId='#1' except select data from table_left where groupId='#11'

一律道理,大家把表table_left中的组#11和表 table_right中的组#2,对列data进行"Except"集运算,如:

复制代码 代码如下:select data from table_left where groupId='#11' except select data from table_right where groupId='#2' select data from table_right where groupId='#2' except select data from table_left where groupId='#11'

只要(#11 à #2 )或 (#2 à #11 State of Qatar的"Except"集运算结果有记录,就认证两组的多少不对等。

两张表的富有组都实行相比较,大家需求通过以下SQL脚本实现,如:

复制代码 代码如下:select distinct a.groupId from table_left a inner join table_right b on b.data=a.data where not exists(select x.data from table_left x where x.groupId=a.groupId except select y.data from table_right y where y.groupId=b.groupId ) and not exists(select x.data from table_right x where x.groupId=b.groupId except select y.data from table_left y where y.groupId=a.groupId )

总体代码:

复制代码 代码如下:View Code use tempdbgoif object_id('table_left') is not null drop table table_leftif object_id('table_right') is not null drop table table_rightgocreate table table_left(groupId nvarchar(5),data nvarchar(10))create table table_right(groupId nvarchar(5),data nvarchar(10))gocreate nonclustered index ix_left on table_left(data)create nonclustered index ix_right on table_right(data)goset nocount ongoinsert into table_right(groupId,data)select '#1','data1' union allselect '#1','data2' union allselect '#1','data3' union allselect '#2','data55' union allselect '#2','data55' union allselect '#3','data91' union allselect '#3','data92' union allselect '#4','data65' union allselect '#4','data66' union allselect '#4','data67' union allselect '#4','data68' union allselect '#4','data69' union allselect '#5','data77' union allselect '#5','data79'insert into table_left(groupId,data)select '#11','data1' union allselect '#11','data2' union allselect '#11','data3' union allselect '#22','data55' union allselect '#22','data57' union allselect '#33','data99' union allselect '#33','data99' union allselect '#44','data66' union allselect '#44','data68' union allselect '#55','data77' union allselect '#55','data78' union allselect '#55','data79'go--select select distinct a.groupId from table_left a inner join table_right b on b.data=a.data where not exists(select x.data from table_left x where x.groupId=a.groupId except select y.data from table_right y where y.groupId=b.groupId ) and not exists(select x.data from table_right x where x.groupId=b.groupId except select y.data from table_left y where y.groupId=a.groupId )

方法1 Vs. 方法2 :

方法1和艺术2都能寻觅表table_left在table_right存在数据完全相等的组#11。但质量角度上,方法2比办法1略高一筹,能够看它们实行进度的计算音讯:

方法1:

图5.

方法2:

图6.

假定,数据量大状态下,那么方法2比办法1更富有无可争辨的独特之处。因为方法1,多五个更新dataPath的局地,数据量随着扩张,这里地点的更新就耗很多的能源;要是dataPath列数据大小抢先900字节,会引致力所不及在dataPath创造索引,影响后边的Select查询品质。

扩展

此地说扩充,首如果本着地点的章程2以来。在当列data的数目大小超过900字节,大概隐含五个数据列要进行比较,看是还是不是留存两组(groupId卡塔尔(قطر‎的各对应列数据一一相等。

图7.

那样的情景,可对字段dataSub1 & dataSub2 创造一个哈希索引,如:

复制代码 代码如下: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_right(dataChecksum) create nonclustered index table_right_cs on table_right(dataChecksum)

末端的select查询语句,在Inner Join 部分稍改变下就可以,如:

复制代码 代码如下: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 )

完全代码:

复制代码 代码如下:View Code use tempdbgoif object_id('table_left') is not null drop table table_leftif object_id('table_right') is not null drop table table_rightgocreate 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))goalter table table_left add dataChecksum as checksum(dataSub1,dataSub2)alter table table_right add dataChecksum as checksum(dataSub1,dataSub2)gocreate nonclustered index ix_table_left_cs on table_left(dataChecksum)create nonclustered index table_right_cs on table_right(dataChecksum)goset nocount ongoinsert into table_right(groupId,dataSub1,dataSub2)select '#1','data1','data7' union allselect '#1','data2','data8' union allselect '#1','data3','data9' union allselect '#2','data55','data4' union allselect '#2','data55','data5' insert into table_left(groupId,dataSub1,dataSub2)select '#11','data1','data7' union allselect '#11','data2','data8' union allselect '#11','data3','data9' union allselect '#22','data55','data0' union allselect '#22','data57','data2' union allselect '#33','data99','data4' union allselect '#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 )

小结

对于那一个主题素材,大概还应该有其它的或更优的解决方法.并且在实际的生育情状中,也许遇见的情形会有所分歧,无论怎么着,要求多解析,多动手多尝试,找到最优的消除措施。

本文由js06金沙官网登录-js06.com-欢迎您发布于计算机,转载请注明出处:根据SQL Server中如何比很多个表的各组数据 图阐述

关键词: