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

巴特西

MSSQL 删除重复数据

最新文章

热门文章