请选择 进入手机版 | 继续访问电脑版
查看: 508|回复: 0

[SQL Server] 消除sql2000里的重复数据

3万

主题

3万

帖子

10万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
100197
发表于 2015-11-19 18:51:24

游标版
declare @id1 int,@oldid int,@e_REcordid int ,@Olde_REcordid int
DECLARE price CURSOR
FOR SELECT id ,E_recordId FROM evaeve order by E_recordId desc
OPEN price
FETCH NEXT FROM price into @oldid,@Olde_REcordid
while @@fetch_status = 0
begin
FETCH NEXT FROM price
into @id1,@e_REcordid

if @Olde_REcordid=@e_REcordid
BEGIN
delete from evaeve where id=@id1
end
set @oldid=@id1
set @Olde_REcordid=@e_REcordid
end
close price
DEALLOCATE price

sql语句版
delete from evaeve where id not in( select max(id) from evaeve group by E_RecordID)

  1. declare @id1 int,@oldid int,@e_REcordid int ,@Olde_REcordid int
  2. DECLARE price CURSOR
  3. FOR SELECT id ,E_recordId FROM evaeve order by E_recordId desc
  4. OPEN price
  5. FETCH NEXT FROM price into @oldid,@Olde_REcordid
  6. while @@fetch_status = 0
  7. begin
  8. FETCH NEXT FROM price
  9. into @id1,@e_REcordid
  10. if @Olde_REcordid=@e_REcordid
  11. BEGIN
  12. delete from evaeve where id=@id1
  13. end
  14. set @oldid=@id1
  15. set @Olde_REcordid=@e_REcordid
  16. end
  17. close price
  18. DEALLOCATE price
  19. delete from evaeve where id not in( select max(id) from evaeve group by E_RecordID)
复制代码


回复

使用道具 举报