SQL Server高分榜pr_PutHighScoreList存储过程开发优化问询

一、前置数据库对象调整与创建 #

1. 为现有HighScores表添加唯一约束 #

防止同一用户出现多条记录，保证数据唯一性：

ALTER TABLE dbo.HighScores
ADD CONSTRAINT UQ_HighScores_User UNIQUE ([User]);

2. 创建排名变动记录表 #

用于记录用户每次分数更新带来的排名变化：

CREATE TABLE dbo.ScoreRankChanges
(
    ChangeId INT IDENTITY(1,1) PRIMARY KEY,
    UserName VARCHAR(255) NOT NULL,
    OldScore INT NULL, -- 新用户首次提交时为NULL
    NewScore INT NOT NULL,
    OldRank INT NULL, -- 新用户首次提交时为NULL
    NewRank INT NOT NULL,
    ChangeTime DATETIME NOT NULL DEFAULT GETDATE()
);

二、原有存储过程存在的问题 #

你提供的初始存储过程存在以下逻辑和语法错误：

• 条件逻辑写反：IF EXISTS判断现有分数高于新提交分数才执行更新，违背了「仅保留最高得分」的要求
• 语法错误：ELSE后多余的SELECT语句不符合语法规范
• 参数长度不匹配：@name定义为nvarchar(20)，与表中varchar(255)的用户名字段长度不符，可能出现字符截断
• 缺少核心功能：没有排名变动的记录逻辑
• 没有错误处理和事务保证，可能出现数据不一致

三、优化后的pr_PutHighScoreList存储过程 #

完整实现需求的存储过程代码如下：

ALTER PROCEDURE [dbo].[pr_PutHighScoreList]
(
    @name AS VARCHAR(255), -- 与表字段长度对齐
    @score AS INT
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON; -- 出错自动回滚事务

    DECLARE 
        @OldScore INT,
        @OldRank INT,
        @NewRank INT;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. 获取用户原有分数和原有排名（如果存在）
        SELECT 
            @OldScore = Score,
            @OldRank = UserRank
        FROM (
            SELECT 
                [User],
                Score,
                RANK() OVER(ORDER BY Score DESC, DateAdded ASC) AS UserRank
            FROM dbo.HighScores
        ) t
        WHERE [User] = @name;

        -- 2. 仅当是新用户，或者新分数高于原有分数时才执行操作
        IF @OldScore IS NULL OR @score > @OldScore
        BEGIN
            -- 合并更新/插入操作，避免重复判断
            MERGE INTO dbo.HighScores AS target
            USING (SELECT @name AS UserName, @score AS NewScore) AS source
            ON target.[User] = source.UserName
            WHEN MATCHED THEN
                UPDATE SET 
                    Score = source.NewScore,
                    DateAdded = GETDATE()
            WHEN NOT MATCHED THEN
                INSERT ([User], Score, DateAdded)
                VALUES (source.UserName, source.NewScore, GETDATE());

            -- 3. 计算更新后用户的新排名
            SELECT @NewRank = UserRank
            FROM (
                SELECT 
                    [User],
                    RANK() OVER(ORDER BY Score DESC, DateAdded ASC) AS UserRank
                FROM dbo.HighScores
            ) t
            WHERE [User] = @name;

            -- 4. 记录排名变动
            INSERT INTO dbo.ScoreRankChanges (UserName, OldScore, NewScore, OldRank, NewRank)
            VALUES (@name, @OldScore, @score, @OldRank, @NewRank);
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- 抛出错误信息，可根据业务需求调整为日志记录
        THROW;
    END CATCH
END

四、功能测试验证 #

以你给出的示例pr_PutHighScoreList 'Ann', 2750测试：

1. 执行前Ann的原有分数为800，原有排名为第14位
2. 执行存储过程后，Ann的分数更新为2750，新排名为第2位（低于Amy的3500，高于Tom的2700）
3. 查看ScoreRankChanges表可看到对应的变动记录，符合需求

内容的提问来源于stack exchange，提问作者Lewis