SQL Server2005中删除重复行

以前写的东西，可以看看思路

 

/**/ /*
删除表中重复行的方法
*/
if   exists ( select   1   from  sys.tables  where  name = ' t_dup ' )
     drop   table  t_dup
go
create   table  t_dup(id  int ,age  int ,name  varchar ( 32 ))
insert   into  t_dup
select   1 , 1 , ' a '
union   all   select   1 , 1 , ' a '
union   all   select   1 , 1 , ' a '
union   all   select   1 , 2 , ' a '
union   all   select   1 , 2 , ' e '
union   all   select   2 , 3 , ' b '
union   all   select   3 , 3 , ' d '
go
select   *   from  t_dup
go
with  t_all  as
    ( select  id,age,name,row_number()  over ( order   by  id)  [ num ]
      from  t_dup),
    t_max  as
    ( select  id,age,name, max (num) num
      from  t_all
      group   by  id,age,name)
select  id,name,age  from  t_all a  where   exists ( select   1   from  t_max  where  num = a.num)