DECLARE @PaperID BIGINT -- 试卷编号
DECLARE @AnserCount INT -- 回答人数
DECLARE @UserID BIGINT -- 创建试卷的用户编号
DECLARE @Point INT -- 用户积分
SET @Point = 10 -- 初始化积分
DECLARE C1 CURSOR FOR -- 声明游标
SELECT TOP 10 PaperID,COUNT(*) as TJCOUNT -- 取得一周内回答次数最多的试卷
FROM Paper
WHERE Type=2 AND AddDate BETWEEN DATEADD(day, -7, getdate()) AND getdate() -- Type=2 表示回答
GROUP BY PaperID
ORDER BY TJCOUNT
OPEN C1 -- 打开游标更新分数
FETCH NEXT FROM C1 INTO @PaperID,@AnserCount
WHILE @@fetch_status = 0
BEGIN
SELECT @UserID=UserID FROM Paper WHERE ID=@PaperID -- 获取创建试卷的用户编号
INSERT INTO PaperPoint(UserID,Point,CurYear,CurMonth) -- 给创建试卷的用户添加相应分数
VALUES(@UserID,@Point,YEAR(GETDATE()),MONTH(GETDATE()))
-- EXEC AddPaperPoint @UserID,@Point,YEAR(GETDATE()),MONTH(GETDATE()) 也可使用存储过程
SET @Point=@Point-1 -- 排名往后的依次减1分
FETCH NEXT FROM C1 INTO @PaperID,@AnserCount
END
CLOSE C1
DEALLOCATE C1
