当前位置:  数据库>sqlserver

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

    来源: 互联网  发布时间:2014-10-11

    本文导语:  开始 前一阵子,在项目中碰到这样一个SQL查询需求,有两个相同结构的表(table_left & table_right),如下: 图1. 检查表table_left的各组(groupId),是否在表table_right中存在有一组(groupId)数据(data)与它的数据(data)完全相等. 如图1. 可以看...

开始

前一阵子,在项目中碰到这样一个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 )

小结

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


    
 
 

您可能感兴趣的文章:

  • WEB前端 iis7站长之家
  • 在Windows XP系统安装SQL server 2000 企业版(图解版)
  • SQL的Join使用图解教程
  • SQL Server 2012 安装图解教程(附sql2012下载地址)
  • SQL2000 全文索引完全图解
  • SQL Server中的执行引擎入门 图解
  • 同一个sql语句 连接两个数据库服务器
  • 刚学的两个简单的SQL语句
  • sql语句中,怎样进行两个日期的时间差,用秒数表示??
  • JAVA JODBC中怎样连续操作两个(或以上)的SQL语句
  • Sql Server 数据类型转换的两个函数
  • Sql Server:多行合并成一行,并做分组统计的两个方法
  • 急问:如何将java.util.Date转换成java.sql.Date,来算出两个日期相差天数?
  • sql server中两个日期相减的例子
  • 我刚学jsp没两天,写了两个jsp程序往sql server2000的数据库里添加中文数据,怎么是乱码啊,(英文好使),100分奉上!!
  • sql中两个日期相减的实例分享
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • SQL Server 2008 事件探查器(SQL SERVER Profiler) 列的说明
  • 高分求java.sql类库的类说明,函数及方法!
  • JDK1.4连接SQL SERVER 2K,用哪个JDBC驱动好点?请说明理由。
  • MSSQL中递归SQL查询语句实例说明-
  • 解析PL/SQL Developer导入导出数据库的方法以及说明
  • sql根据表名获取字段及对应说明
  • Oracle 高速批量数据加载工具sql*loader使用说明
  • SQL Server数据库重命名、数据导出的方法说明
  • sql server中添加字段、删除字段、修改字段说明
  • SQL对冗余数据的删除重复记录只保留单条的说明
  • SQL Server存储过程的基础说明
  • SQL SERVER中各类触发器的完整语法及参数说明
  • mysql sql_mode="" 的作用说明
  • 关于 SQL Server ErrorLog 错误日志说明
  • SQL语句实例说明 方便学习mysql的朋友
  • java命名空间java.sql接口statement的类成员方法: executeupdate定义及介绍
  • 请问,这是什么错误!java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][Named Pipes]??????? SQL Server?虽然分少,但一定给,只要您是前5名回复者中最好的以为!
  • java命名空间java.sql接口connection的类成员方法: nativesql定义及介绍
  • SQL查询分析工具 SQL Workbench/J
  • java命名空间java.sql接口preparedstatement的类成员方法: executeupdate定义及介绍
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • java命名空间java.sql接口rowid的类成员方法: getbytes定义及介绍
  • SQL Server统计SQL语句执行时间的脚本
  • java命名空间java.sql接口ref的类成员方法: getbasetypename定义及介绍
  • SQL客户端软件 PKLite SQL Client
  • java命名空间java.sql接口databasemetadata的类成员方法: getsqlkeywords定义及介绍
  • SQL语句实现SQL Server 2000及Sql Server 2005日志收缩(批量)
  • java命名空间java.sql接口rowid的类成员方法: tostring定义及介绍
  • SQL客户端管理工具 SQuirreL SQL Client
  • java命名空间javax.sql.rowset接口joinrowset的类成员方法: getwhereclause定义及介绍
  • 如何处理此错误:java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]没有执行可选特性


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3