ROW_NUMBER

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为条件进行操作。