日度归档:2019年6月14日

mssql sqlserver 使用sql脚本获取指定数据表中重复记录的三种方法分享


摘要:
下文讲述使用sql脚本获取数据表中重复记录的方法分享
实验环境:sql server 2008 R2



方法1:使用not in过滤出相关数据

 select * from [maomao365.com] 
 where sort  not in  
 (
select sort   from [maomao365.com] 
  group by  sort 
  having count(info)   = 1 
)
 


方法2:使用in获取相关数据

   select * from [maomao365.com] 
 where sort    in  
 (
select sort   from [maomao365.com] 
  group by  sort 
  having count(info)   > 1 
)


方法3:使用row_number 开窗函数获取重复数据

 select * from (
   select row_number()  over(partition by [sort] order by info desc ) as keyId,*      from [maomao365.com]  ) t where t.keyid >1