• 在SQL Server中如何比较两个表的各组数据


    开始


    前一阵子,在项目中碰到这样一个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数据完全相等,如:

    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 )的"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 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
    create nonclustered index ix_left on table_left(data)
    create nonclustered index ix_right on table_right(data)
    
    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
    --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 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 )

    小结


    对于这个问题,可能还有其他的或更优的解决方法.而且在实际的生产环境中,可能碰到的情况会有所不同,无论如何,需要多分析,多动手多实验,找到最优的解决方法。

  • 相关阅读:
    数据库基础+重置root密码
    Bug定级
    测试用例和测试方法
    测试基础
    HDOJ-1010 Tempter of the Bone(dfs)
    POJ
    HDU-2089 不要62 (数位DP)
    Happy!
    LOJ-1422 万圣节服装
    数字三角形(数塔问题)
  • 原文地址:https://www.cnblogs.com/wghao/p/3029904.html
Copyright © 2020-2023  润新知