SQL 中如何删除重复(每列数据都重复)的记录，只保留一行？

如果数据表没有做好约束，那么数据库中难免会遇到数据重复的情况。今天就遇到这么个看起来简单却又费神的问题---如何去重。

------期间感谢微信公众号“有关SQL”的博主大牛提供的指导和建议。大家可以关注下他的公众号。

借鉴下大神的思路，去除重复的核心思想就两个：

1：找到重复记录，删除他们；

2：找到非重复记录，保留他们

两个思想，操作方式不同，取决于重复记录与非重复记录的行数倾斜度。

情况一：数据表本身数据量不大的情况，如何去除重复

一：创建测试表

 CREATE TABLE test(id INT ,NAME VARCHAR(20)) 

二：插入数据

INSERT INTO test VALUES (1,'001')
INSERT INTO test VALUES (2,'002')
INSERT INTO test VALUES (3,'003')
INSERT INTO test VALUES (3,'003')
INSERT INTO test VALUES (3,'003')
INSERT INTO test VALUES (3,'003')

　刚开始用SQL的开窗函数对数据行进行排序

 SELECT t.id,t.NAME,row_number() OVER (partition by t.id ORDER BY t.id ) AS NUM FROM test AS t 

效果如下:

然后发现其实并没有什么用，跟源表无法匹配关联起来，准确的删除4行重复记录里的3行记录。想来想去，唯有强大的游标才能帮到我，因为游标是对数据逐行操作的！！！

想到这点后，咋们说做就做：

三：游标代码

declare @max int,@id int 
declare cur_rows cursor local for select id,count(*) from test group by id having count(*) > 1 
open cur_rows 
fetch cur_rows into @id,@max 
while @@fetch_status=0 
begin 
select @max = @max -1 
set rowcount @max 
delete from test where id= @id 
fetch cur_rows into @id,@max 
end 
close cur_rows 
set rowcount 0

最后效果：

当然，如果只有悉数的几种重复数据行，你也可以这样玩

CREATE TABLE #table1  (id INT,NAME VARCHAR(20),COT1 INT,DEL int )--创建临时表

INSERT INTO #table1--将重复行的信息以及重复行数存放到临时表
SELECT DISTINCT 
 t.id,t.NAME,t.COT1,t.COT1-1
FROM 
(SELECT id,NAME,COUNT(*)OVER (PARTITION BY id) AS COT1  FROM  test) AS t
WHERE t.COT1>1

效果如图：

随后执行删除语句：

DELETE TOP (4) a FROM  test AS a WHERE a.id=3

也可达到效果。

情况二：数据表数据量超大，如何去除重复

如果原表重复记录太多，删掉他们肯定费事费时。

delete 操作既要寻址，还要写 Log ，造成锁表时间太长，影响并发。

那么将这些非重复记录先取出来，放入新表，整理完毕后，丢弃原表，重命名新表为原表名即可；

如果非重复记录很多，将他们取出来就又要花很长时间了，设想 1 亿的数据量导出来，不现实了吧。那么只有保留他们，而将重复记录删掉即可。

这里假设数据表数据量很大，已经达到千万级别甚至过亿数量级别情况下的做法（将重复数据取出，整理后导回源表）：

1：将重复数据取出

SELECT 
DISTINCT id,NAME 
INTO #TEMP
FROM 
(
    SELECT 
    id,NAME,
    ROW_NUMBER()OVER(PARTITION BY id ORDER BY NAME ASC) AS RNK
    FROM Test WITH(NOLOCK) 
) TMP 
WHERE RNK>1

2：删除源表中所有重复记录（暂时不保留）

WHILE (EXISTS(SELECT TOP 1 1 FROM Test ts INNER JOIN #TEMP TP ON TP.id = ts.id ))
BEGIN     
    DELETE TOP (10000) Test     
    FROM Test     
    INNER JOIN #TEMP TP ON Test.id = TP.id 
END 

3：将临时表中存储的备份数据，插回源表（单行数据）

INSERT INTO Test(id,NAME) 

最后彻底删除临时中间表

DROP TABLE #TEMP

其中需要注意的是：

a：具体操作中尽量使用实体表，这里中间表只是方便演示；

b：使用while和delete top 的目的是为了保证一个事务足够小，不至于日志爆表；