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

2,810 0
  • 完整數據
    +---------+-------+
    | 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 |
    +---------+------+---------+-------+

 

  • 取得 user_id = 15 的記錄及名次
    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 |
    +------+---------+-------+

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料