有些表设计没有标识,没有主键,删除起来一直是个麻烦的事情,而CTE的运用却让这件事情变得简单。
create table CreDelete
(
id int identity(1,1),
name varchar(10)
)
go
insert into CreDelete
select '张三' as name
union all select '王2'
union all select '张三'
union all select '王2'
union all select '张1'
union all select '张2'
union all select '张1'
union all select '张2'
go
select * from CreDelete
;with mycte as
(
select ROW= ROW_NUMBER() over(PARTITION by name order by id),* from CreDelete
)delete from mycte where ROW>1
SQL2005CTE做无标识重复记录删除,让你的sql看起来更简单
最新推荐文章于 2021-03-01 18:00:00 发布