SQL 7.9 游标，row_number() ，update的另一种写法

来源：互联网发布：手拉鸡专卖网淘宝付款编辑：程序博客网时间：2024/04/30 12:50

row_number() over(order by id)

--------------------------------------------------------------------------

if object_id('[test]') is not null drop table [test]
go
create table [test] (id varchar(4))
insert into [test]
select '0001' union all
select '0001' union all
select '0001' union all
select '0001' union all
select '0001'

DECLARE @I INT
SELECT @I=MIN(ID)-1 FROM test
UPDATE test SET @I=@I+1,id=RIGHT('0000'+LTRIM(@I),4)
SELECT * FROM TEST

--------------------------------------------------------------------------

DECLARE @I INT
SELECT @I=MIN(ID) FROM test
UPDATE test SET id='800-'+RIGHT('0000'+LTRIM(@I-1),4),@I=@I+1
————————————————————————————————

IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
CREATE TABLE test
(
    id int
)
GO
INSERT test
SELECT 1 union all
SELECT 1 union all
SELECT 1 union all
SELECT 1 union all
SELECT 1
--查询

declare @i int
set @i=1
declare cur cursor for select id from test
open cur
fetch cur
while @@fetch_status=0
begin
    update test set id=@i where current of cur
    set @i=@i+1
    fetch cur
end
close cur
deallocate cur

select * from test