1. 云栖社区>
  2. 技术资料>
  3. 正文

删除Table表中的重复行的方法_MsSql

作者:用户 来源:互联网 时间:2016-06-08 19:16:36

table重复行

删除Table表中的重复行的方法_MsSql - 摘要: 本文讲的是删除Table表中的重复行的方法_MsSql, 利用SQL Server 2005的新功能NOW_NUMBER和CTE可以很好的实现. 举例说明如下: 建立测试数据: 复制代码 代码如下: create table Dup1 ( Col1 int null, Col2 varcha

利用SQL Server 2005的新功能NOW_NUMBER和CTE可以很好的实现.
举例说明如下:
建立测试数据:
复制代码 代码如下:

create table Dup1
(
Col1 int null,
Col2 varchar(20) null
)
insert into Dup1 values
(1, 'aaa'),
(2, 'aaa'),
(2, 'aaa'),
(2, 'aaa'),
(3, 'bbb'),
(3, 'bbb'),
(4, 'ccc'),
(4, 'ddd'),
(5, 'eee')
select * from Dup1

可以查看到重复的数据有:
复制代码 代码如下:

SELECT Col1, Col2, COUNT(*) AS DupCountFROM Dup1GROUP BY Col1, Col2HAVING COUNT(*) > 1

20100608002211701.jpg
接下来介绍如何delete掉重复的数据:
1.NOW_NUMBER:SQL Server 2005添加了很好用的RANKING函数(NOW_NUMBER,RANK,DENSE_RANK,NTILE),利用NOW_NUMBER()OVER(PARTITION GY)最为直接,也最为方便,不能修改表或者产生多余的列.
首先会分配一个列号码,以Col1,Col2组合来分区排序.
复制代码 代码如下:

SELECT Col1, Col2,ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rnFROM Dup1


得到的序号如下:
20100608002211200.jpg 
很明显的是重复列都分组分割排序,只需要delete掉排序序号>1的即可.
复制代码 代码如下:

--用到CTE
WITH DupsD
AS (
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM Dup1
)
DELETE DupsD
WHERE rn > 1;
--或者
DELETE A FROM (
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM Dup1) A WHERE A.rn>1

2.创建一个标识键唯一的表记一列.
复制代码 代码如下:

ALTER TABLE dbo.Dup1
ADD
PK INT IDENTITY
NOT NULL
CONSTRAINT PK_Dup1 PRIMARY KEY;
SELECT *
FROM Dup1;

删除找出与Col1,Col2相同并且比Dup1.PK大的记录,也就是保留重复值中PK最小的记录.
复制代码 代码如下:

DELETE Dup1
WHERE EXISTS ( SELECT *
FROM Dup1 AS D1
WHERE D1.Col1 = Dup1.Col1
AND D1.Col2 = Dup1.Col2
AND D1.PK > Dup1.PK );

3.select distant into,这种方法借助一个新的table,把不重复的结果集转移到新table中.
复制代码 代码如下:

SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1;select * from NoDups

建议采用第一种和第三种方法,第一种多见于T-SQL的编程中,第三种在ETL中常常使用.

以上是云栖社区小编为您精心准备的的内容,在云栖社区的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索table 重复行 mssql 删除重复记录、mssql 删除重复数据、mssql 限制删除行数、excel删除重复行、excel如何删除重复行,以便于您获取更多的相关知识。

稳定可靠、可弹性伸缩的在线数据库服务,全球最受欢迎的开源数据库之一

6款热门基础云产品6个月免费体验;2款产品1年体验;1款产品2年体验

弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率

开发者常用软件,超百款实用软件一站式提供