sqlserver 删除重复记录处理（转）

注：此处“重复”非完全重复,意为某字段数据重复
　　HZT表结构
ID　int
Title　nvarchar(50)
AddDate　datetime
　　数据
一. 查找重复记录
　　1. 查找全部重复记录
Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

2. 过滤重复记录(只显示一条)
Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)
注：此处显示ID最大一条记录
二. 删除重复记录
　　1. 删除全部重复记录（慎用）
Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

　　2. 保留一条（这个应该是大多数人所需要的）
Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)
　　注：此处保留ID最大一条记录

其它相关:

 删除重复记录有大小关系时,保留大或小其中一个记录

  --  > --> (Roy)生成測試數據  
  
  if     not     object_id  (  '  Tempdb..#T  '  )   is     null  
    drop     table   #T
  Go  
  Create     table   #T(  [  ID  ]     int  ,  [  Name  ]     nvarchar  (  1  ),  [  Memo  ]     nvarchar  (  2  ))
  Insert   #T
  select     1  ,N  '  A  '  ,N  '  A1  '     union     all  
  select     2  ,N  '  A  '  ,N  '  A2  '     union     all  
  select     3  ,N  '  A  '  ,N  '  A3  '     union     all  
  select     4  ,N  '  B  '  ,N  '  B1  '     union     all  
  select     5  ,N  '  B  '  ,N  '  B2  '  
  Go  

  --  I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条  
  方法1:
  delete   a   from   #T a   where     exists  (  select     1     from   #T   where   Name  =  a.Name   and   ID  <  a.ID)
方法2:
  delete   a   from   #T a   left     join   (  select     min  (ID)ID,Name   from   #T   group     by   Name) b   on   a.Name  =  b.Name   and   a.ID  =  b.ID 
  where   b.Id   is     null  
方法3:
  delete   a   from   #T a   where   ID   not     in   (  select     min  (ID)   from   #T   where   Name  =  a.Name)
方法4(注:ID为唯一时可用):
  delete   a   from   #T a   where   ID   not     in  (  select     min  (ID)  from   #T   group     by   Name)
方法5:
  delete   a   from   #T a   where   (  select     count  (  1  )   from   #T   where   Name  =  a.Name   and   ID  <  a.ID)  >  0  
方法6:
  delete   a   from   #T a   where   ID  <>  (  select     top     1   ID   from   #T   where   Name  =  a.name   order     by   ID)
方法7:
  delete   a   from   #T a   where   ID  >any  (  select   ID   from   #T   where   Name  =  a.Name)
  select     *     from   #T

生成结果:
  /*  
ID     Name Memo
----------- ---- ----
1      A  A1
4      B  B1

(2 行受影响)
  */  


  --  II、Name相同ID保留最大的一条记录:    
方法1:
  delete   a   from   #T a   where     exists  (  select     1     from   #T   where   Name  =  a.Name   and   ID  >  a.ID)
方法2:
  delete   a   from   #T a   left     join   (  select     max  (ID)ID,Name   from   #T   group     by   Name) b   on   a.Name  =  b.Name   and   a.ID  =  b.ID
  where   b.Id   is     null  
方法3:
  delete   a   from   #T a   where   ID   not     in   (  select     max  (ID)   from   #T   where   Name  =  a.Name)
方法4(注:ID为唯一时可用):
  delete   a   from   #T a   where   ID   not     in  (  select     max  (ID)  from   #T   group     by   Name)
方法5:
  delete   a   from   #T a   where   (  select     count  (  1  )   from   #T   where   Name  =  a.Name   and   ID  >  a.ID)  >  0  
方法6:
  delete   a   from   #T a   where   ID  <>  (  select     top     1   ID   from   #T   where   Name  =  a.name   order     by   ID   desc  )
方法7:
  delete   a   from   #T a   where   ID  <any  (  select   ID   from   #T   where   Name  =  a.Name)
  select     *     from   #T
  /*  
ID     Name Memo
----------- ---- ----
3      A  A3
5      B  B2

(2 行受影响)
  */  


  --  3、删除重复记录没有大小关系时，处理重复值    
  --  > --> (Roy)生成測試數據  
   
  if     not     object_id  (  '  Tempdb..#T  '  )   is     null  
    drop     table   #T
  Go  
  Create     table   #T(  [  Num  ]     int  ,  [  Name  ]     nvarchar  (  1  ))
  Insert   #T
  select     1  ,N  '  A  '     union     all  
  select     1  ,N  '  A  '     union     all  
  select     1  ,N  '  A  '     union     all  
  select     2  ,N  '  B  '     union     all  
  select     2  ,N  '  B  '  
  Go  

方法1:
  if     object_id  (  '  Tempdb..#  '  )   is     not     null  
    drop     table   #
  Select     distinct     *     into   #   from   #T  --  排除重复记录结果集生成临时表#    
  truncate     table   #T  --  清空表    
  insert   #T   select     *     from   #    --  把临时表#插入到表#T中  
  
  --  查看结果  
  select     *     from   #T

  /*  
Num     Name
----------- ----
1      A
2      B

(2 行受影响)
  */  

  --  重新执行测试数据后用方法2  
  方法2:
  alter     table   #T   add   ID   int     identity  --  新增标识列  
  go  
  delete   a   from   #T a   where     exists  (  select     1     from   #T   where   Num  =  a.Num   and   Name  =  a.Name   and   ID  >  a.ID)  --  只保留一条记录  
  go  
  alter     table   #T   drop     column   ID  --  删除标识列  
  
  --  查看结果  
  select     *     from   #T

  /*  
Num     Name
----------- ----
1      A
2      B

(2 行受影响)

  */  

  --  重新执行测试数据后用方法3  
  方法3:
  declare   Roy_Cursor   cursor   local   for  
  select     count  (  1  )  -  1  ,Num,Name   from   #T   group     by   Num,Name   having     count  (  1  )  >  1  
  declare     @con     int  ,  @Num     int  ,  @Name     nvarchar  (  1  )
  open   Roy_Cursor
  fetch     next     from   Roy_Cursor   into     @con  ,  @Num  ,  @Name  
  while     @@Fetch_status  =  0  
  begin   
    set     rowcount     @con  ;
    delete   #T   where   Num  =  @Num     and   Name  =  @Name  
    set     rowcount     0  ;
    fetch     next     from   Roy_Cursor   into     @con  ,  @Num  ,  @Name  
  end  
  close   Roy_Cursor
  deallocate   Roy_Cursor

  --  查看结果  
  select     *     from   #T
  /*  
Num     Name
----------- ----
1      A
2      B

(2 行受影响)
  */ 

查看更多关于sqlserver 删除重复记录处理（转）的详细内容...