很久没有写过SQL了,今天写了一个循环数据更新,以备以后查看:
DECLARE @Count int ,@SortIndex int,@strSQL varchar(500)
SET @Count=(SELECT COUNT(*) FROM SCO_BehindMenu WHERE ParentId='f320a77e-096b-4929-82df-86cc41ffc3dc')
SET @SortIndex=0
WHILE(@SortIndex<@Count)
BEGIN
SET @strSQL='UPDATE SCO_BehindMenu SET SortIndex='+STR(@SortIndex+1)+' WHERE MenuId=('
SET @strSQL=@strSQL+'SELECT TOP 1 MenuId FROM SCO_BehindMenu WHERE ParentId=''f320a77e-096b-4929-82df-86cc41ffc3dc'' AND MenuId NOT IN (SELECT TOP '+STR(@SortIndex)+' MenuId FROM SCO_BehindMenu WHERE ParentId=''f320a77e-096b-4929-82df-86cc41ffc3dc'' ORDER BY MenuId) ORDER BY MenuId)'
SET @SortIndex=@SortIndex+1
print @strSQL
EXEC(@strSQL)
END