删除 SQL Server 表中的重复行
来源:互联网 发布:java电脑版86安装包 编辑:程序博客网 时间:2024/04/30 17:37
Microsoft SQL Server 表不应该包含重复行和非唯一主键。重复的主键违反了实体完整性,在关系系统中是不允许的。SQL Server 有各种强制执行实体完整性的机制,包括索引、唯一约束、主键约束和触发器。尽管如此,在某些情况下还可能会出现重复的主键;如果出现此类情况,就必须清除重复主键。出现重复主键的情形之一是,在 SQL Server 外部的非关系数据中存在重复的主键,在导入这些数据时没有强制执行主键唯一性。出现重复主键的另一种情形来自数据库设计错误,如没有对每张表强制执行实体完整性。本文讨论如何查找和删除表中重复的主键。
在该示例中,我们将使用下表,它具有重复的主键值。在该表中,主键是两列(col1、col2)。我们无法创建唯一索引或主键约束,因为这两行具有重复的主键。该过程演示如何识别和删除重复的主键。
第一步是识别哪些行具有重复的主键值:
这将为表中的每组重复的主键值返回一行。此结果中的最后一列是特定主键值的重复数。
但如果表中有多组完全不同的重复的主键值,则逐个删除它们会很费时间。在这种情况下,可使用下面的方法:
1.首先,运行上面的 GROUP BY 查询来确定有多少组重复的主键值及每组的重复数。
2.选择重复的键值放入临时表中。例如:
3.选择重复的行放入临时表中,以清除进程中的重复值。例如:
4.此时,holddups 表应有唯一的主键;但是,如果 tableName 有重复的主键而行唯一(如上面的 SSN 示例),情况就不是这样了。请验证 holddups 中的各个主键是否唯一,是否没有主键重复而行唯一的情况。如果是这样,您必须停在该处,确定对于给定重复的主键值,您希望保留哪些行。例如,以下查询:
应为各行返回计数 1。如果结果是 1,请继续执行下面的步骤 5。如果不是 1,则存在主键重复而行唯一的情况,且需要您决定要保存哪些行。通常,这将需要舍弃行或为此行创建新的唯一的主键值。为 holddups 表中每个此种重复主键执行这两个步骤之一。
5.从原始表中删除重复的行。例如:
6.将唯一行放回原始表中。例如:
在该示例中,我们将使用下表,它具有重复的主键值。在该表中,主键是两列(col1、col2)。我们无法创建唯一索引或主键约束,因为这两行具有重复的主键。该过程演示如何识别和删除重复的主键。
首先先创建测试数据表
<span style="font-family:Microsoft YaHei;">create table tableName(col1 int, col2 int, col3 char(50)) insert into tableName select 1, 1, 'data value one' union all select 1, 1, 'data value one' union all select 1, 2, 'data value two' </span>
第一步是识别哪些行具有重复的主键值:
<span style="font-family:Microsoft YaHei;">SELECT col1, col2, count(*) as 重复数 FROM tableName GROUP BY col1, col2 HAVING count(*) > 1 </span>
这将为表中的每组重复的主键值返回一行。此结果中的最后一列是特定主键值的重复数。
<span style="font-family:Microsoft YaHei;">col1 col2 重复数 ----------- ----------- ----------- 1 1 2 </span>
<span style="font-family:Microsoft YaHei;">set rowcount 1 delete from tableName where col1=1 and col2=1 </span>
<span style="font-family:Microsoft YaHei;">delete top(1) from tableName where col1=1 and col2=2 </span>
在删除行之前,还应该验证是否整行重复。虽然整行重复不太可能发生,但可能主键值重复,而整行不重复。例如一个将身份证号码作为主键的表,该表有两个具有相同号码的不同的人(即行),但每个人有唯一的属性。在这种情况下,任何引起重复主键的问题可能还引起在行中放入有效的唯一的数据。在删除该数据之前,应该将该数据复制出来并保存下来以进行研究和适当的调整。
但如果表中有多组完全不同的重复的主键值,则逐个删除它们会很费时间。在这种情况下,可使用下面的方法:
1.首先,运行上面的 GROUP BY 查询来确定有多少组重复的主键值及每组的重复数。
2.选择重复的键值放入临时表中。例如:
<span style="font-family:Microsoft YaHei;">SELECT col1, col2, col3=count(*) INTO holdkey FROM tableName GROUP BY col1, col2 HAVING count(*) > 1 </span>
3.选择重复的行放入临时表中,以清除进程中的重复值。例如:
<span style="font-family:Microsoft YaHei;">SELECT DISTINCT t1.* INTO holddups FROM tableName, holdkey WHERE t1.col1 = holdkey.col1 AND t1.col2 = holdkey.col2 </span>
4.此时,holddups 表应有唯一的主键;但是,如果 tableName 有重复的主键而行唯一(如上面的 SSN 示例),情况就不是这样了。请验证 holddups 中的各个主键是否唯一,是否没有主键重复而行唯一的情况。如果是这样,您必须停在该处,确定对于给定重复的主键值,您希望保留哪些行。例如,以下查询:
<span style="font-family:Microsoft YaHei;">SELECT col1, col2, count(*) FROM holddups GROUP BY col1, col2 </span>
应为各行返回计数 1。如果结果是 1,请继续执行下面的步骤 5。如果不是 1,则存在主键重复而行唯一的情况,且需要您决定要保存哪些行。通常,这将需要舍弃行或为此行创建新的唯一的主键值。为 holddups 表中每个此种重复主键执行这两个步骤之一。
5.从原始表中删除重复的行。例如:
<span style="font-family:Microsoft YaHei;">DELETE tableName FROM t1, holdkey WHERE t1.col1 = holdkey.col1 AND t1.col2 = holdkey.col2 </span>
6.将唯一行放回原始表中。例如:
<span style="font-family:Microsoft YaHei;">INSERT tableName SELECT * FROM holddups </span>
1 0
- 删除SQL Server表中的重复行
- 删除 SQL Server 表中的重复行
- Sql Server 删除表中的重复数据
- 如何删除 SQL Server 表中的重复行
- 如何删除 SQL Server 表中的重复行
- 如何删除 SQL Server 表中的重复行
- 如何删除 SQL Server 表中的重复行
- 如何删除 SQL Server 表中的重复行(1)
- 如何删除 SQL Server 表中的重复行(2)
- 详解如何删除SQL Server表中的重复行
- SQL SERVER删除重复行
- SQL Server删除重复行
- SQL Server 2000删除表中的重复记录
- SQL Server 2000删除表中的重复记录
- 搜索/删除 Sql server 表中的重复值。
- 数据库SQL server 删除一张表中的重复记录
- 删除sql server中的重复记录
- 如何删除 SQL Server 表中的重复行(转自Microsoft)
- NSObject的load和initialize方法
- OpenCV中的CV_IMPL是什么意思?
- 盘点苹果2016WWDC精彩看点, iOS 10官方细节要来了
- OSI 七层协议参考模型 与 TCP/IP协议 实现网络模型
- 除去字符串里相同的字符
- 删除 SQL Server 表中的重复行
- 长文干货!走近人脸检测:从VJ到深度学习(上)(下)
- 万恶的开源druid,去die
- wincache xcache 性能测试
- uva12661
- msp430的printf函数的简单移植
- IEEE极限编程之The pipeline-动态规划思想
- LeetCode *** 209. Minimum Size Subarray Sum
- ubuntu 部署L2TP亲测