MSSQL 删除重复数据

--删除重复数据，无标识列情况
if object_id(N'test',N'U') is not null 
  drop table test
go
create table test(  
  id INT,
  n  NVARCHAR(20)
)
go 
INSERT INTO test VALUES (1,'a')
INSERT INTO test VALUES (1,'b')
INSERT INTO test VALUES (2,'c')
go
with t as 
(
  select ROW_NUMBER( )  
    --按照id分组，删除id重复数据
      OVER(partition by id order by id, n) as rowid, * from test
)
--删除
delete from t where rowid>1
--查看原数据
select * from test

--有标识列情况  有很多种方法  详情参考： https://www.cnblogs.com/lyhabc/p/3278827.html
if object_id(N'test',N'U') is not null 
  drop table test
go
create table test(  
  id INT identity ,
  n  NVARCHAR(20)
)
go 
INSERT INTO test VALUES ('a')
INSERT INTO test VALUES ('a')
INSERT INTO test VALUES ('c')
go
select * from Test
delete a from test a where id>any(select id from test where n=a.n) 
--delete a from test a where id <> (select top 1 id from test where n=a.n order by id )
select * from test