初学者 - 获取sql中的前3名（考虑相同的总分）

Question

Hi I am trying to obtain the top 3 ranking and pass them to php, and if total score of two ID's are the same, both have the same rank. I tried to use limit 0, 3 and rank() but only get the the first 3 records without taking same total score into account.

╔════╦════════════╗
║ ID ║ TOTALSCORE ║
╠════╬════════════╣
║  7 ║         20 ║
║  4 ║         20 ║
║  6 ║         18 ║
║  9 ║         18 ║
║  1 ║         16 ║
╚════╩════════════╝

Then, the ranking should be

╔══════╦════╗
║ RANK ║ ID ║
╠══════╬════╣
║    1 ║  7 ║
║    1 ║  4 ║
║    2 ║  6 ║
║    2 ║  9 ║
║    3 ║  1 ║
╚══════╩════╝

Answer 1

Since MySQL do not support Window Function like any RDBMS has, you can still simulate what DENSE_RANK() can do by using user define variables, eg

SELECT  a.ID, a.TotalScore, b.Rank
FROM    TableName a
        INNER JOIN
        (
            SELECT  TotalScore, @rn := @rn + 1 Rank
            FROM
                    (
                        SELECT  DISTINCT TotalScore
                        FROM    TableName
                    ) a, (SELECT @rn := 0) b
            ORDER   BY TotalScore DESC
        ) b ON  a.TotalScore = b.TotalScore
WHERE   Rank <= 3

• SQLFiddle Demo
• SQLFiddle Demo (Another Example)

OUTPUT

╔════╦════════════╦══════╗
║ ID ║ TOTALSCORE ║ RANK ║
╠════╬════════════╬══════╣
║  7 ║         20 ║    1 ║
║  4 ║         20 ║    1 ║
║  6 ║         18 ║    2 ║
║  9 ║         18 ║    2 ║
║  1 ║         16 ║    3 ║
╚════╩════════════╩══════╝