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

最新推荐文章于 2024-04-17 16:58:28 发布

weixin_34106122

最新推荐文章于 2024-04-17 16:58:28 发布

阅读量236

点赞数

原文链接：https://my.oschina.net/haoye/blog/1818831

版权

为什么80%的码农都做不了架构师？>>>

方法一

第一步：查询重复记录

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

转载于:https://my.oschina.net/haoye/blog/1818831