首页 > 其他 > 详细

ROW_NUMBER

时间:2016-02-24 09:30:40      阅读:187      评论:0      收藏:0      [点我收藏+]

16:23 2015/4/16 删除重复数据,连续被两位同事问到完全相同的重复记录如何删除只保留一条

技术分享
create table del_samerecords
(id int,remark varchar(10))
go
insert into del_samerecords
select 1,a
go 2
go
insert into del_samerecords
select 2,b
go 3
go
--方式1
;with a as
(
select ROW_NUMBER() over(partition by id order by id) as rankid,* from del_samerecords
)
delete from a where rankid>1
--方式2
while(1=1)
begin
    delete top (1) del_samerecords
    from del_samerecords,
    (select id from del_samerecords group by id having count(*)>1) b
    where del_samerecords.id=b.id
    if @@ROWCOUNT=0
    return
end
View Code

当然最容易理解的是将唯一记录insert到临时表,然后delete原表对应数据,再将临时表中的数据insert到原表
15:03 2016/1/27 随机修改表中记录

技术分享
--比如表里有1W条数据,随机修改表里100条
DROP TABLE test
CREATE TABLE test(id INT IDENTITY,idd int)
DECLARE @i INT=1
WHILE(@i<1000)
BEGIN
    INSERT INTO test(idd) select @i
    SET @i=@i+1
END

;WITH cte AS 
(SELECT *,ROW_NUMBER() OVER (ORDER BY NEWID()) rankid FROM test
)
UPDATE cte SET idd=999999
WHERE rankid<=10

SELECT * FROM test
WHERE idd=999999
View Code

两个例子都有借助ROW_NUMBER得到排序rankid,然后以rankid为条件进行操作。

ROW_NUMBER

原文:http://www.cnblogs.com/Uest/p/5163323.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!