MySQL实现按bedrag降序、spelersnr降序排名列的技术求助

我来帮你搞定这个排名需求~ 你的核心需求是生成POSITIE排名列，规则是按bedrag从大到小排序，相同bedrag时spelersnr更大的排前面，原SQL存在几个小问题，我来一步步修正：

问题分析 #

你的原始SQL有几个关键问题：

• 排序方向错误：ORDER BY mbedrag是升序，不符合“从大到小”的要求；
• 缺少次要排序条件：没有添加spelersnr DESC，无法实现相同bedrag时的优先级规则；
• 变量排名的执行顺序风险：直接在主查询中使用@curRank，虽然MySQL通常会先执行ORDER BY，但把排序后的结果作为子查询再计算排名，逻辑更清晰也更稳妥；
• 未处理无罚款玩家：当玩家没有对应的boetes记录时，mbedrag会是NULL，排序时会被排到最后，用COALESCE转为0更合理。

最终解决方案 #

方案1：连续排名（每个玩家排名唯一） #

这个方案完全匹配你的需求：相同bedrag时spelersnr大的排前面，每个玩家获得连续的唯一排名：

SELECT 
    spelersnr, 
    naam, 
    mbedrag,
    @curRank := @curRank + 1 AS POSITIE
FROM (
    -- 先获取每个玩家的最大罚款金额，并按规则排序
    SELECT 
        s.spelersnr, 
        s.naam, 
        COALESCE(MAX(b.bedrag), 0) AS mbedrag
    FROM spelers s
    LEFT JOIN boetes b ON s.spelersnr = b.spelersnr
    GROUP BY s.spelersnr, s.naam
    ORDER BY mbedrag DESC, s.spelersnr DESC
) AS ranked_data,
-- 初始化排名变量
(SELECT @curRank := 0) AS rank_init;

方案2：相同金额同排名（跳过中间排名） #

如果你希望相同bedrag的玩家获得相同排名，下一个不同金额的玩家直接跳过中间排名（比如1,1,3），可以用这个版本：

SELECT 
    spelersnr, 
    naam, 
    mbedrag,
    POSITIE
FROM (
    SELECT 
        spelersnr,
        naam,
        mbedrag,
        -- 根据前一行的金额判断排名
        CASE 
            WHEN @prev_mbedrag = mbedrag THEN @curRank
            ELSE @curRank := @curRank + 1
        END AS POSITIE,
        @prev_mbedrag := mbedrag
    FROM (
        SELECT 
            s.spelersnr, 
            s.naam, 
            COALESCE(MAX(b.bedrag), 0) AS mbedrag
        FROM spelers s
        LEFT JOIN boetes b ON s.spelersnr = b.spelersnr
        GROUP BY s.spelersnr, s.naam
        ORDER BY mbedrag DESC, s.spelersnr DESC
    ) AS sorted_data,
    (SELECT @curRank := 0, @prev_mbedrag := NULL) AS rank_vars
) AS final_ranked;

关键说明 #

• 用LEFT JOIN替代子查询获取mbedrag，性能更优，也更易维护；
• COALESCE(MAX(b.bedrag), 0)确保没有罚款的玩家mbedrag为0，避免NULL干扰排序；
• 排序规则严格遵循你的要求：ORDER BY mbedrag DESC, s.spelersnr DESC，先按金额降序，金额相同时按玩家号降序。

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