关于删除重复行(保留一行)
假设目标表 person为:
1,使用临时表
method 1
select distinct * into #t from person -- 把所有不重复的行填充到临时表#t
truncate table person --删除源表
insert person select * from #t --复制临时表到源表
drop table #t -- 删除临时表
PS:该方法存在局限性,当数据库的表中存在image,text类型字段时会报错,提示该类型字段无法进行distinct操作
method 2
select *,count(*) as count into #t from person
group by name,age,department
having count(*)>1 --复制重复的行(单条记录)到临时表#t
delete from person where exists(
select * from #t b where person.age=b.age and
person.department=b.department and person.Name=b.Name) -- 把源表有重复的行都删掉
insert into person( age,Name,department)
select age,Name,department from #t --把重复的行(单条记录)插入源表
drop table #t
method 3 : 带索引的临时表
select * into #t from person where 1<>1--复制表结构
create unique index myindex on #t(Name,age,department) with ignore_dup_key --设置索引,忽略重复的列
insert #t select * from person --
truncate table person
insert into person select * from #t
truncate table #t
2 使用游标
declare @name as nvarchar(10),@icount as int=0
declare person_cursor cursor
for select name,count(*) from person group by name having count(*)>1 -- 这里只筛选name列做个说明
open person_cursor
fetch next from person_cursor into @name,@icount
while @@FETCH_STATUS=0
begin
delete top(@icount-1) from person where name=@name --(删除icount-1条记录,保留一条)
fetch next from person_cursor into @name,@icount
end
close person_cursor
deallocate person_cursor
-- PS:可以使用 'SET @ICOUNT=@ICOUNT-1 SET ROWCOUNT @ICOUNT' 代替DELETE中的TOP,但不建议这么做.
-- 语法:set rowcountnumber | @number
--参数:整数
--作用 :停止指令之前要处理的行数
--set rowcount 0 关闭该选项,即处理所有行