使用 MySQL 制作排行榜計算排名

+---------+-------+
| user_id | score |
+---------+-------+
|       1 |    84 |
|       2 |    45 |
|       3 |    85 |
|       4 |    13 |
|       5 |    45 |
|       6 |    48 |
|       7 |    54 |
|       8 |    98 |
|       9 |    65 |
|      10 |    98 |
|      11 |    54 |
|      12 |    42 |
|      13 |    84 |
|      14 |    97 |
|      15 |    24 |
|      16 |    58 |
|      17 |    23 |
|      18 |    48 |
|      19 |    57 |
|      20 |    23 |
+---------+-------+

SELECT row_num, rank, user_id, score FROM
(SELECT @rownum := 0, @rnk := null, @curscore := null) r,
(
    SELECT
        user_id, score,
        (@rownum := @rownum + 1) AS row_num,
        (@rnk := IF(@curscore = score, @rnk, @rownum)) AS rank,
        @curscore := score
    FROM test_table
    ORDER BY score DESC, user_id
) AS t, 

結果:

+---------+------+---------+-------+
| row_num | rank | user_id | score |
+---------+------+---------+-------+
|       1 |    1 |       8 |    98 |
|       2 |    1 |      10 |    98 |
|       3 |    3 |      14 |    97 |
|       4 |    4 |       3 |    85 |
|       5 |    5 |       1 |    84 |
|       6 |    5 |       6 |    84 |
|       7 |    5 |      13 |    84 |
|       8 |    8 |       9 |    65 |
|       9 |    9 |      16 |    58 |
|      10 |   10 |      19 |    57 |
|      11 |   11 |       7 |    54 |
|      12 |   11 |      11 |    54 |
|      13 |   13 |      18 |    48 |
|      14 |   14 |       2 |    45 |
|      15 |   14 |       5 |    45 |
|      16 |   16 |      12 |    42 |
|      17 |   17 |      15 |    24 |
|      18 |   18 |      17 |    23 |
|      19 |   18 |      20 |    23 |
|      20 |   20 |       4 |    13 |
+---------+------+---------+-------+

SELECT * FROM test_table ORDER BY score DESC, user_id LIMIT 10

結果:

+---------+-------+
| user_id | score |
+---------+-------+
|       8 |    98 |
|      10 |    98 |
|      14 |    97 |
|       3 |    85 |
|       1 |    84 |
|       6 |    84 |
|      13 |    84 |
|       9 |    65 |
|      16 |    58 |
|      19 |    57 |
+---------+-------+

SELECT row_num, rank, user_id, score FROM
(SELECT @rownum := 0, @rnk := null, @curscore := null) r,
(
    SELECT
        user_id, score,
        (@rownum := @rownum + 1) AS row_num,
        (@rnk := IF(@curscore = score, @rnk, @rownum)) AS rank,
        @curscore := score
    FROM test_table
    ORDER BY score DESC, user_id LIMIT 10
) AS t

結果:

+---------+------+---------+-------+
| row_num | rank | user_id | score |
+---------+------+---------+-------+
|       1 |    1 |       8 |    98 |
|       2 |    1 |      10 |    98 |
|       3 |    3 |      14 |    97 |
|       4 |    4 |       3 |    85 |
|       5 |    5 |       1 |    84 |
|       6 |    5 |       6 |    84 |
|       7 |    5 |      13 |    84 |
|       8 |    8 |       9 |    65 |
|       9 |    9 |      16 |    58 |
|      10 |   10 |      19 |    57 |
+---------+------+---------+-------+

SELECT rank, user_id, score FROM
(SELECT @rownum := 0, @rnk := null, @curscore := null) r,
(
    SELECT
        user_id, score,
        (@rownum := @rownum + 1) AS row_num,
        (@rnk := IF(@curscore = score, @rnk, @rownum)) AS rank,
        @curscore := score
    FROM test_table
    ORDER BY score DESC, user_id
) AS t 
WHERE user_id = 15 LIMIT 1

結果:

+------+---------+-------+
| rank | user_id | score |
+------+---------+-------+
|   17 |      15 |    24 |
+------+---------+-------+