MySQL (JDBC) - 基于列值的排行榜排名

Question

目标:

• 查找由“viewedUserId”标识的给定用户的排名

排名:

• 经验越高，等级越高(越低)。
• 如果两个或更多用户拥有相同的体验，则优先获得该体验的人将获得更高的排名。 (updated_at 存储 currentTimeMillis (bigint/long) 的列)
• 排名不应该并列。 (任何用户都不能与其他用户拥有相同的排名)

问题:

• 是否可以在查询中计算排名，这样我们就不必手动遍历并比较每个存储的用户的结果。 (理想情况下，我们希望返回 1 个具有排名的结果)

代码:

public static int getRank(Connection connection, int viewedUserId) throws SQLException {
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        statement = connection.createStatement();
        resultSet = statement.executeQuery("SELECT user_id FROM scores ORDER BY experience DESC, updated_at ASC");
        int rank = 1;
        while(resultSet.next()) {
            int userId = resultSet.getInt("user_id");
            if(userId == viewedUserId)
                return rank;
            rank++;
        }
        throw new SQLException("Failed to find rank for user: " + viewedUserId);
    } finally {
        DatabaseUtils.close(statement, resultSet);
    }
}

关注:

• 以上代码将按照我的预期运行，但性能是一个主要因素。使用上面的代码并不理想，因为我们的“分数”表将包含数十万行。

Answer 1

看这个问题:https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table

他们有一张这样的 table :

Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

并添加排名:

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

结果

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

您还可以添加 WHERE 子句来过滤单个用户。