基于SQL Server中如何比较两个表的各组数据 图解说明

时间:2021-05-02

开始

前一阵子,在项目中碰到这样一个SQL查询需求,有两个相同结构的表(table_left & table_right),如下:

图1.

检查表table_left的各组(groupId),是否在表table_right中存在有一组(groupId)数据(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列数据连串起来(如,图4.把table_left的组#11的列data连串起来成"data1-data2-data3"),其他分组(包含表table_right)以此方法实现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 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),data nvarchar(10))
create table table_right(groupId nvarchar(5),data nvarchar(10))
go
alter table table_left add dataPath nvarchar(200)
alter table table_right add dataPath nvarchar(200)
go
create nonclustered index ix_left on table_left(dataPath)
create nonclustered index ix_right on table_right(dataPath)
go
set nocount on
go
insert into table_right(groupId,data)
select '#1','data1' union all
select '#1','data2' union all
select '#1','data3' union all
select '#2','data55' union all
select '#2','data55' union all
select '#3','data91' union all
select '#3','data92' union all
select '#4','data65' union all
select '#4','data66' union all
select '#4','data67' union all
select '#4','data68' union all
select '#4','data69' union all
select '#5','data77' union all
select '#5','data79'
insert into table_left(groupId,data)
select '#11','data1' union all
select '#11','data2' union all
select '#11','data3' union all
select '#22','data55' union all
select '#22','data57' union all
select '#33','data99' union all
select '#33','data99' union all
select '#44','data66' union all
select '#44','data68' union all
select '#55','data77' union all
select '#55','data78' union all
select '#55','data79'
go
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)

方法2:

通过SQL Sever提供的集运算符"Except",判断两组非重复的数据。如果两组针对对方都不存在非重复的数据,就说明这两组数据完全相等。如,表table_left中的组#11和表 table_right中的组#1,对列data进行"Except"集运算,无任是(#11 à #1)进行Except集运算,还是(#1 à #11 )进行Except集合运算,都返回空结果,这就说明组#1 和#11的data数据完全相等,如:

复制代码 代码如下:

声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

相关文章