sql散记

关于删除重复行（保留一行）

 假设目标表 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  关闭该选项，即处理所有行