SQL：获取数据库中的玩家排名

Question

我正在为我正在构建的游戏建立一个高分体系。我认为，或者说希望这是一个容易回答的问题。我需要得到球员对其他球员的“排名”。假设有3名球员，你的分数是最低的。所以你的排名是#3。我如何在SQL中获得它？我想我使用了COUNT，但我不确定如何遍历每一行来找到我的球员的分数。我知道我将按订购。

这是我的基本表：

SELECT [ID]
  ,[Email]
  ,[High_Score]
  ,[Board_Name]
  ,[Difficulty]
  ,[Player_Time]
FROM [Rune_Master].[dbo].[High_Scores]

我需要按Board_Name、难度进行筛选，因为每个棋盘有3个难度，玩家可以在每个棋盘上按3种不同的方式进行排名。最终，尽管这是基于他们与其他人相比的高_分数。每个棋盘的每个难度只有一分。因此，分数将因棋盘难度而异。我希望这一切都有意义

这就是我目前的情况：

--Player Data
DECLARE @PlayerScore AS BIGINT
DECLARE @PlayerRank AS NVARCHAR(100)

IF EXISTS(SELECT [High_Score] FROM [dbo].[High_Scores] WHERE [Board_Name] = @Board AND [Difficulty] = @Difficulty AND [Email] = @Email) BEGIN
    SET @PlayerScore = (SELECT [High_Score] FROM [dbo].[High_Scores] WHERE [Board_Name] = @Board AND [Difficulty] = @Difficulty AND [Email] = @Email)
    SET @PlayerRank = (SELECT TOP 1 COUNT([High_Score]) FROM [dbo].[High_Scores] WHERE [Board_Name] = @Board AND [Difficulty] = @Difficulty ORDER BY [High_Score] DESC)
END ELSE BEGIN
    SET @PlayerScore = 0;
    SET @PlayerRank = 'UnRanked';
END

--Top 5 Player Scores
SELECT DISTINCT TOP 5
    [High_Score] AS [Score],
    [Player_Time],
    [Board_Name],
    [Email],
    @PlayerScore AS 'Player_Score',
    @PlayerRank AS 'Player_Rank'
FROM
    [dbo].[High_Scores]
WHERE
    [Board_Name] = @Board AND
    [Difficulty] = @Difficulty
ORDER BY [High_Score] DESC, [Player_Time] DESC, [Email]

Answer 1

如果有人需要，这里有答案。谢谢大家的帮助。

代码：

    DECLARE
    @Board AS NVARCHAR(100) = 'Templo_Mayor',
    @Difficulty AS INT = 0,
    @Email AS NVARCHAR(500) = 'Computer',
    @PlayerRank AS NVARCHAR(100)


CREATE TABLE #Rank
(
    High_Score BIGINT,
    Row# BIGINT,
    EMAIL NVARCHAR(500)    
 )
INSERT INTO #Rank (High_Score, Row#, EMAIL) 
SELECT [High_Score], ROW_NUMBER() OVER(ORDER BY [High_Score] DESC) AS Row#, [EMAIL] FROM [dbo].[High_Scores] WHERE [Board_Name] = @Board AND [Difficulty] = @Difficulty ORDER BY [High_Score] DESC

SELECT Row# FROM #Rank WHERE EMAIL = @Email ORDER BY [High_Score] DESC
DROP TABLE #Rank

所以为了解释我所做的：

1. 声明了我的变量
2. 创建了一个临时表，我要用它来构建我的排名表（临时表）
3. 用添加行计数器的数据填充我的排名表
4. 通过电子邮件从我的排名表中选择我的用户的排名，因此我只返回他们的排名。