Create Table TEST (id Int, CID Int) Insert TEST Select 1, 1 Union All Select 2, 2 Union All Select 3, 3 Union All Select 4, 4 Union All Select 5, 5 GO Update TEST Set CID = CID +1Select * From TEST GO Drop Table TEST --Result /* id CID 1 2 2 3 3 4 4 5 5 6 */
恩恩,就是要用游標實現的方法'===============================//重新整理写入排序 Set Rs=Conn.ExeCute("select ID from Sys_Reg order by TopID") i=1 Do While Not Rs.Eof Conn.ExeCute("Update Sys_Reg Set TopID="&i&" Where ID = "& Rs(0)&"") i=i+1 Rs.MoveNext Loop Rs.Close Set Rs=Nothing 这是原来的ASP代码,现在改在存储过程中完成! 目的是解决因各种原因导致排序ID不连续,重新按写入连续的排序ID
這個不需要用游標實現的,一個SQL語句就可以搞定的。Create Table TEST (id Int, CID Int) Insert TEST Select 1, 1 Union All Select 2, 3 Union All Select 3, 5 Union All Select 4, 7 Union All Select 5, 9 GO Update A Set CID = (Select Count(*) From TEST Where CID <= A.CID) From TEST ASelect * From TEST GO Drop Table TEST --Result /* id CID 1 1 2 2 3 3 4 4 5 5 */
如果你的TopID順序和ID是一致的,可以這麼更新Update A Set TopID = (Select Count(*) From Sys_Reg Where id <= A.id) From Sys_Reg A
如果你的TopID順序和ID是不完全一致的,而你想按照原來的TopID的順序更新的話,可以這麼更新 Update A Set TopID = (Select Count(*) From Sys_Reg Where TopID < A.TopID Or (TopID = A.TopID And id < A.id)) + 1 From Sys_Reg A
呵呵,感觉逻辑好复杂,我还在测试,怕有BUG
set nocount no delcare @id int, @cid intselect id,cid from table order by id open authors_cursorfetch next from authors_cursor into @id,@cidprint 'id cid' while @@fetch_status =0 begin set @cid = @cid +1 print cast(@id as varchar(10)) + ' ' + cast(@cid as varchar(10)) fetch next from authors_cursor into @id,@cid end close authors_cursor deallocate authors_cursorset go
(id Int,
CID Int)
Insert TEST Select 1, 1
Union All Select 2, 2
Union All Select 3, 3
Union All Select 4, 4
Union All Select 5, 5
GO
Update TEST Set CID = CID +1Select * From TEST
GO
Drop Table TEST
--Result
/*
id CID
1 2
2 3
3 4
4 5
5 6
*/
Set Rs=Conn.ExeCute("select ID from Sys_Reg order by TopID")
i=1
Do While Not Rs.Eof
Conn.ExeCute("Update Sys_Reg Set TopID="&i&" Where ID = "& Rs(0)&"")
i=i+1
Rs.MoveNext
Loop
Rs.Close
Set Rs=Nothing
这是原来的ASP代码,现在改在存储过程中完成!
目的是解决因各种原因导致排序ID不连续,重新按写入连续的排序ID
(id Int,
CID Int)
Insert TEST Select 1, 1
Union All Select 2, 3
Union All Select 3, 5
Union All Select 4, 7
Union All Select 5, 9
GO
Update A Set CID = (Select Count(*) From TEST Where CID <= A.CID) From TEST ASelect * From TEST
GO
Drop Table TEST
--Result
/*
id CID
1 1
2 2
3 3
4 4
5 5
*/
Update A Set TopID = (Select Count(*) From Sys_Reg Where TopID < A.TopID Or (TopID = A.TopID And id < A.id)) + 1 From Sys_Reg A
delcare @id int, @cid intselect id,cid from table order by id
open authors_cursorfetch next from authors_cursor
into @id,@cidprint 'id cid'
while @@fetch_status =0
begin
set @cid = @cid +1
print cast(@id as varchar(10)) + ' ' + cast(@cid as varchar(10)) fetch next from authors_cursor
into @id,@cid
end
close authors_cursor
deallocate authors_cursorset
go