sql server 表去除重复行

1、有唯一列，通过唯一列最大或最小方式删除重复记录

1 delete from zczjDataTemp
2   where cdnum in( select cdnum from zczjDataTemp group by  cdnum having count(cdnum ) > 1) and 
3  ID not in(select  max(ID) from zczjDataTemp group by  cdnum having count(cdnum ) > 1 )

2、无唯一列使用ROW_NUMBER()函数删除重复记录

1 Delete T From
2  (Select Row_Number() Over(Partition By [cdnum] order By [ID]) As RowNumber,* From zczjDataTemp)T
3  Where T.RowNumber > 1

确认SQL

1 select T.* From
2   (Select Row_Number() Over(Partition By [cdnum] order By [ID]) As RowNumber,* From zczjDataTemp)T
3   Where T.RowNumber = 1

 

posted @ 2021-07-08 23:09  laoyang01  阅读(666)  评论(0)  编辑  收藏  举报