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

Question

利用 CTE 和 ROW_NUMBER() 在原表删除重复数据
不用临时表的做法：
1、建测试数据
create table TableA(f1 int,f2 varchar(10))
goinsert 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')
go2、以下代码完成删除，并显示结果
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 c1select * from TableA3、结果如下：
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?

Answer 1

create table TableA(f1 int,f2 varchar(10))
goinsert 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>1select* from TableA
/*
f1          f2
----------- ----------
1           abc
2           abcd
3           xxxx(3 行受影响)*/

Answer 2

drop table TableA
create table TableA(f1 int,f2 varchar(10))
goinsert 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')
gowith cte as
(
 select *,row=row_number() over(partition by f1 order by getdate()) from TableA
)
delete cte where row>1select * from TableA
/*
f1          f2
----------- ----------
1           abc
2           abcd
3           xxxx(3 行受影响)*/