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

数据运维 2023-04-18 穿过生命散发芬芳手机阅读

开始前一阵子，在项目中碰到这样一个SQL查询需求，有两个相同结构的表(table_left table_right)，如下：图1. 检查表table_left的各组(groupId)，是否在表table_right中存在有一组(groupId)数据(dat

                        <p>开始</p>

前一阵子，在项目中碰到这样一个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列数据连串的结果，如：

完整代码：

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

同样道理，我们把表table_left中的组#11和表 table_right中的组#2，对列data进行"Except"集运算，如:

只要(#11 à #2 )或 (#2 à #11 )的"Except"集运算结果有记录，就说明两组的数据不相等。

两张表的所有组都进行比较，我们需要通过以下SQL脚本实现，如：

完整代码：

方法1和方法2都能找出表table_left在table_right存在数据完全相等的组#11。但性能角度上，方法2比方法1略胜一筹，可以看它们执行过程的统计信息：

方法1：