利用 CTE 和 ROW_NUMBER() 在原表删除重复数据

[selectplayer]

利用 CTE 和 ROW_NUMBER() 在原表删除重复数据
不用临时表的做法：
1、建测试数据
create table TableA(f1 int,f2 varchar(10))
go

insert TableA values(1,'abc')
insert TableA values(1,'abc')
insert TableA values(1,'abc')
insert TableA values(2,'abcd')
insert TableA values(2,'abcd')
insert TableA values(3,'xxxx')
go

2、以下代码完成删除，并显示结果
declare @p1 int,@p2 varchar(10)

declare c1 cursor for
select f1,f2 from TableA
open c1
fetch next from c1 into @p1,@p2
while @@fetch_status = 0
begin
WITH [CTE_ROWID] AS
(select row_number() over(order by f1) as ROWID,* from TableA where f1 = @p1 and f2 = @p2)
delete [CTE_ROWID]
where ROWID > 1
fetch next from c1 into @p1,@p2
end
close c1
deallocate c1

select * from TableA

3、结果如下：
f1 f2
----------- ----------
1 abc
2 abcd
3 xxxx

4、删除测试表
drop TableA

注：之所以使用游标，是因为语句
WITH [CTE_ROWID] AS
(select row_number() over(order by f1) as ROWID,* from TableA)
DELETE [CTE_ROWID]
FROM [CTE_ROWID] a WHERE
exists (select * from [CTE_ROWID] where f1 = a.f1 and f2 = a.f2 and ROWID < a.ROWID)

执行结果错误，将删去全部数据。

在2005的SP3(Build 4035)和SP4(Build5000)测试结果一致。
在2008(Build1600)测试结果也是如此。
这可以算一个BUG?

[dawugui]

你这个必须用临时表.

[dawugui]

[Quote=引用 2 楼 dawugui 的回复:]
你这个必须用临时表.
[/Quote]你这个建议用临时表.快.

[中国风]

[--小F--]

谢谢分享。

[黄_瓜]

create table TableA(f1 int,f2 varchar(10))
go

insert TableA values(1,'abc')
insert TableA values(1,'abc')
insert TableA values(1,'abc')
insert TableA values(2,'abcd')
insert TableA values(2,'abcd')
insert TableA values(3,'xxxx')
go

;with t as
(
select row_number() over(partition by f1 order by f2) as id, * from TableA
)
delete  from t where id>1

select* from TableA
/*
f1          f2
----------- ----------
1           abc
2           abcd
3           xxxx

(3 行受影响)

*/

[pengxuan]

drop table TableA
create table TableA(f1 int,f2 varchar(10))
go

insert TableA values(1,'abc')
insert TableA values(1,'abc')
insert TableA values(1,'abc')
insert TableA values(2,'abcd')
insert TableA values(2,'abcd')
insert TableA values(3,'xxxx')
go

with cte as
(
 select *,row=row_number() over(partition by f1 order by getdate()) from TableA
)
delete cte where row>1

select * from TableA
/*
f1          f2
----------- ----------
1           abc
2           abcd
3           xxxx

(3 行受影响)

*/