SQL 删除表中的重复记录,只保留其中一条。

原创
2018/05/25 17:35
阅读数 578

方法一

第一步:查询重复记录

SELECT * FROM TableName  --表名
WHERE RepeatFiled IN (  
    SELECT RepeatFiled  --重复字段
    FROM TableName  
    GROUP BY RepeatFiled  
    HAVING COUNT(RepeatFiled) > 1  
)  

把重复条数大于1的全部查询出来。

第二步:删除重复记录,只保留一条

SELECT * FROM TableName  
WHERE RepeatFiled IN (  
    SELECT RepeatFiled  
    FROM TableName  
    GROUP BY RepeatFiled  
    HAVING COUNT(RepeatFiled) > 1  
    AND  
    ID NOT IN (  
        SELECT MIN(ID)  
        FROM TableName  
        GROUP RepeatFiled  
        HAVING COUNT(RepeatFiled) > 1      
    )  
)  

在第一步的基础上继续过滤,把最小值留下。

方法二

declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 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 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部