基于EF的用户XP排名查询：获取目标用户及前后1名的排名信息

LINQ（EF Core）实现 #

首先定义用于存储结果的模型类：

public class UserRankDetail
{
    public int Rank { get; set; }
    public string Name { get; set; }
    public int XP { get; set; }
}

public class UserRankResult
{
    public UserRankDetail Current { get; set; }
    public UserRankDetail Previous { get; set; }
    public UserRankDetail Next { get; set; }
}

然后编写LINQ查询，利用EF Core支持的窗口函数实现排名：

// 替换成你的DbContext实例和目标用户名
var targetUserName = "Alice";
using var dbContext = new YourDbContext();

// 生成带排名的用户数据集（数据库端执行）
var rankedUsers = dbContext.Users
    .Select(u => new UserRankDetail
    {
        Rank = EF.Functions.DenseRank().Over(orderBy: u => u.XP descending, u => u.Name ascending),
        Name = u.Name,
        XP = u.XP
    })
    .AsQueryable();

// 获取目标用户的排名信息
var currentUser = await rankedUsers.FirstOrDefaultAsync(u => u.Name == targetUserName);
if (currentUser == null)
{
    // 处理用户不存在的情况
    return null;
}

// 查询前后一名用户
var previousUser = await rankedUsers.FirstOrDefaultAsync(u => u.Rank == currentUser.Rank - 1);
var nextUser = await rankedUsers.FirstOrDefaultAsync(u => u.Rank == currentUser.Rank + 1);

var result = new UserRankResult
{
    Current = currentUser,
    Previous = previousUser,
    Next = nextUser
};

说明 #

• 使用DenseRank()确保相同XP的用户排名连续，若需要跳过重复名次（比如两个用户XP相同排第2，下一个用户排第4），替换为Rank()即可
• 添加Name ascending作为次要排序条件，避免XP相同时排名出现歧义
• 用AsQueryable()将排名逻辑下推到SQL Server执行，比本地排序性能更优

SQL查询实现 #

如果需要直接编写SQL，可使用CTE结合窗口函数：

DECLARE @TargetUserName NVARCHAR(100) = 'Alice';

WITH RankedUsers AS (
    SELECT 
        Name,
        XP,
        DENSE_RANK() OVER(ORDER BY XP DESC, Name ASC) AS UserRank
    FROM Users
)
SELECT
    curr.UserRank AS CurrentRank,
    curr.Name AS CurrentName,
    prev.UserRank AS PreviousRank,
    prev.Name AS PreviousName,
    next.UserRank AS NextRank,
    next.Name AS NextName
FROM RankedUsers curr
LEFT JOIN RankedUsers prev ON curr.UserRank = prev.UserRank + 1
LEFT JOIN RankedUsers next ON curr.UserRank = next.UserRank - 1
WHERE curr.Name = @TargetUserName;

说明 #

• LEFT JOIN确保即使目标用户是第一名或最后一名，查询仍能返回结果（前后字段为NULL）
• 同样，可将DENSE_RANK()替换为RANK()切换排名规则

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