set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*
填充用户评价等级
*/
ALTER PROC [dbo].[Fill_Userinfos_BuyerRank_SellerRank]
@userid VARCHAR(12) = NULL
AS
BEGIN
DECLARE @rankCount INT
SET @rankCount = 0
DECLARE cursor1 CURSOR
FOR
SELECT a.UserID
FROM C2CEDU.dbo.UserInfos a
INNER JOIN dbo.UserBases b ON a.UserID = b.UserID
WHERE b.Status <> 400
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @userid
WHILE @@fetch_status = 0 --判断是否成功获取数据
BEGIN
-- 更新用户作为买家等级 更新卖家等级
DECLARE @sellerrankvalue INT
SELECT @sellerrankvalue = ISNULL(SUM(rankvalue), 0)
FROM [C2CEDU].[dbo].[UserRankRecords]
WHERE RankType = 1
AND ToUserID = @userid
DECLARE @buyerrankvalue INT
SELECT @buyerrankvalue = ISNULL(SUM(rankvalue), 0)
FROM [C2CEDU].[dbo].[UserRankRecords]
WHERE RankType = 0
AND ToUserID = @userid
UPDATE dbo.UserInfos
SET BuyerRank = ( SELECT ISNULL(SUM(rankvalue), 0)
FROM [C2CEDU].[dbo].[UserRankRecords]
WHERE RankType = 0
AND ToUserID = @userid
) ,
SellerRank = ( SELECT ISNULL(SUM(rankvalue), 0)
FROM [C2CEDU].[dbo].[UserRankRecords]
WHERE RankType = 1
AND ToUserID = @userid
) ,
BuyerRankInfoID = CASE WHEN @buyerrankvalue >= 0
AND @buyerrankvalue <= 3
THEN 41 --0级
WHEN @buyerrankvalue > 3
AND @buyerrankvalue <= 10
THEN 1 --1级
WHEN @buyerrankvalue > 10
AND @buyerrankvalue <= 40
THEN 3 --2级
WHEN @buyerrankvalue > 40
AND @buyerrankvalue <= 90
THEN 5 --3级
WHEN @buyerrankvalue > 90
AND @buyerrankvalue <= 150
THEN 7 --4级
WHEN @buyerrankvalue > 150
AND @buyerrankvalue <= 250
THEN 9 --5级
WHEN @buyerrankvalue > 250
AND @buyerrankvalue <= 500
THEN 11--6级
WHEN @buyerrankvalue > 500
AND @buyerrankvalue <= 1000
THEN 13 --7级
WHEN @buyerrankvalue > 1000
AND @buyerrankvalue <= 2000
THEN 15 --8级
WHEN @buyerrankvalue > 2000
AND @buyerrankvalue <= 5000
THEN 17 --9级
WHEN @buyerrankvalue > 5000
AND @buyerrankvalue <= 10000
THEN 19--10级
WHEN @buyerrankvalue > 10000
AND @buyerrankvalue <= 20000
THEN 21 --11级
WHEN @buyerrankvalue > 20000
AND @buyerrankvalue <= 50000
THEN 23 --12级
WHEN @buyerrankvalue > 50000
AND @buyerrankvalue <= 100000
THEN 25 --13级
WHEN @buyerrankvalue > 100000
AND @buyerrankvalue <= 200000
THEN 27--14级
WHEN @buyerrankvalue > 200000
AND @buyerrankvalue <= 500000
THEN 29 --15级
WHEN @buyerrankvalue > 500000
AND @buyerrankvalue <= 1000000
THEN 31 --16级
WHEN @buyerrankvalue > 1000000
AND @buyerrankvalue <= 2000000
THEN 33 --17级
WHEN @buyerrankvalue > 2000000
AND @buyerrankvalue <= 5000000
THEN 35 --18级
WHEN @buyerrankvalue > 5000000
AND @buyerrankvalue <= 10000000
THEN 37--19级
WHEN @buyerrankvalue > 10000000
AND @buyerrankvalue <= 100000000
THEN 39 --20级
END ,
SellerRankInfoID = CASE WHEN @sellerrankvalue >= 0
AND @sellerrankvalue <= 3
THEN 42 --0级
WHEN @sellerrankvalue > 3
AND @sellerrankvalue <= 10
THEN 2 --1级
WHEN @sellerrankvalue > 10
AND @sellerrankvalue <= 40
THEN 4 --2级
WHEN @sellerrankvalue > 40
AND @sellerrankvalue <= 90
THEN 6 --3级
WHEN @sellerrankvalue > 90
AND @sellerrankvalue <= 150
THEN 8 --4级
WHEN @sellerrankvalue > 150
AND @sellerrankvalue <= 250
THEN 10 --5级
WHEN @sellerrankvalue > 250
AND @sellerrankvalue <= 500
THEN 12--6级
WHEN @sellerrankvalue > 500
AND @sellerrankvalue <= 1000
THEN 14 --7级
WHEN @sellerrankvalue > 1000
AND @sellerrankvalue <= 2000
THEN 16 --8级
WHEN @sellerrankvalue > 2000
AND @sellerrankvalue <= 5000
THEN 18 --9级
WHEN @sellerrankvalue > 5000
AND @sellerrankvalue <= 10000
THEN 20--10级
WHEN @sellerrankvalue > 10000
AND @sellerrankvalue <= 20000
THEN 22 --11级
WHEN @sellerrankvalue > 20000
AND @sellerrankvalue <= 50000
THEN 24 --12级
WHEN @sellerrankvalue > 50000
AND @sellerrankvalue <= 100000
THEN 26 --13级
WHEN @sellerrankvalue > 100000
AND @sellerrankvalue <= 200000
THEN 28--14级
WHEN @sellerrankvalue > 200000
AND @sellerrankvalue <= 500000
THEN 30 --15级
WHEN @sellerrankvalue > 500000
AND @sellerrankvalue <= 1000000
THEN 32 --16级
WHEN @sellerrankvalue > 1000000
AND @sellerrankvalue <= 2000000
THEN 34 --17级
WHEN @sellerrankvalue > 2000000
AND @sellerrankvalue <= 5000000
THEN 36 --18级
WHEN @sellerrankvalue > 5000000
AND @sellerrankvalue <= 10000000
THEN 38--19级
WHEN @sellerrankvalue > 10000000
AND @sellerrankvalue <= 100000000
THEN 40 --20级
END
WHERE UserID = @userid
END
FETCH NEXT FROM cursor1 INTO @userid
CLOSE cursor1
DEALLOCATE cursor1
END
本文转自博客园张占岭（仓储大叔）的博客，原文链接：刚写完的一个用户遍历更新的SQL存储过程,分享一下吧，如需转载请自行联系原博主。
