27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #Tmp
(
Name NVARCHAR(50),
TotalRank INT,
categoryID INT,
categoryRank INT
)
INSERT INTO #Tmp
( Name ,
TotalRank ,
categoryID ,
categoryRank
)
VALUES ( N'AAA' , 1 , 1 , 1 ),
( N'BBB' , 2 , 1 , 2 ),
( N'CCC' , 3 , 1 , 3 ),
( N'DDD' , 4 , 1 , 4 ),
( N'EEE' , 5 , 2 , 1 ),
( N'FFF' , 6 , 2 , 2 ),
( N'GGG' , 7 , 2 , 3 )
DECLARE @Name NVARCHAR(50) = 'BBB',
@NewTotalRank INT = 4,
@CurrentTotalRank INT
SELECT @CurrentTotalRank = TotalRank
FROM #Tmp
WHERE Name = @Name
SELECT @CurrentTotalRank,@NewTotalRank
IF @CurrentTotalRank > @NewTotalRank --排名升了
UPDATE #Tmp SET TotalRank = TotalRank + 1
WHERE TotalRank >= @NewTotalRank AND TotalRank < @CurrentTotalRank
ELSE --排名降了
UPDATE #Tmp SET TotalRank = TotalRank - 1
WHERE TotalRank > @CurrentTotalRank AND TotalRank <= @NewTotalRank
UPDATE #Tmp SET TotalRank = @NewTotalRank
WHERE Name = @Name