如何在SQL联赛排名查询中添加固定位置（POS）列？

嘿，我懂你遇到的问题了——你之前用@rownum变量得到的不是真正的联赛排名，而是分组查询时的行序号，这是因为MySQL的执行顺序搞的鬼：变量递增是在SELECT阶段执行的，而ORDER BY是最后一步才处理的，所以变量赋值的时候结果还没排序，序号自然不对。

解决方案1：基础版（按顺序生成连续排名） #

核心思路是先把你的排名统计结果完全计算好并排序，再在外面套一层查询来生成排名序号。这样变量是在已经排好序的结果上递增，得到的就是正确的POS列：

SET @rownum := 0;
SELECT 
    @rownum := @rownum + 1 AS POS,
    Team,
    P,
    W,
    D,
    L,
    F,
    A,
    GD,
    Pts
FROM (
    -- 这里是你原来的统计逻辑，已经处理好分组和排序
    SELECT 
        t.tname AS Team, 
        Sum(tot.P) AS P,
        Sum(tot.W) AS W,
        Sum(tot.D) AS D,
        Sum(tot.L) AS L, 
        SUM(tot.F) as F,
        SUM(tot.A) AS A,
        SUM(tot.GD) AS GD,
        SUM(tot.Pts) AS Pts 
    FROM(
        SELECT 
            hteam Team, 
            1 P, 
            IF(hscore > ascore,1,0) W, 
            IF(hscore = ascore,1,0) D, 
            IF(hscore < ascore,1,0) L, 
            hscore F, 
            ascore A, 
            hscore-ascore GD, 
            CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS 
        FROM games
        UNION ALL
        SELECT 
            ateam Team, 
            1 P, 
            IF(hscore < ascore,1,0) W, 
            IF(hscore = ascore,1,0) D, 
            IF(hscore > ascore,1,0) L, 
            ascore F, 
            hscore A, 
            ascore-hscore GD, 
            CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS 
        FROM games
    ) as tot 
    JOIN teams t ON tot.Team=t.id 
    GROUP BY t.id, t.tname  -- 加上t.id更严谨，避免tname重复（虽然你设了unique，但好习惯）
    ORDER BY SUM(tot.Pts) DESC, SUM(tot.GD) DESC, SUM(tot.F) DESC
) AS ranked_teams;

解决方案2：进阶版（处理同分并列排名） #

如果你的联赛规则允许同分球队并列排名（比如积分、净胜球、进球数都相同的球队排名一样），可以用多变量来追踪上一支球队的成绩，动态生成排名：

SET @prev_points := NULL;
SET @prev_gd := NULL;
SET @prev_f := NULL;
SET @rank := 0;
SET @row_num := 0;

SELECT 
    -- 对比当前球队和上一支的成绩，决定是否使用相同排名
    CASE 
        WHEN @prev_points = Pts AND @prev_gd = GD AND @prev_f = F THEN @rank
        ELSE @rank := @row_num
    END AS POS,
    Team,
    P,
    W,
    D,
    L,
    F,
    A,
    GD,
    Pts
FROM (
    -- 同样是原来的统计子查询
    SELECT 
        t.tname AS Team, 
        Sum(tot.P) AS P,
        Sum(tot.W) AS W,
        Sum(tot.D) AS D,
        Sum(tot.L) AS L, 
        SUM(tot.F) as F,
        SUM(tot.A) AS A,
        SUM(tot.GD) AS GD,
        SUM(tot.Pts) AS Pts 
    FROM(
        SELECT 
            hteam Team, 
            1 P, 
            IF(hscore > ascore,1,0) W, 
            IF(hscore = ascore,1,0) D, 
            IF(hscore < ascore,1,0) L, 
            hscore F, 
            ascore A, 
            hscore-ascore GD, 
            CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS 
        FROM games
        UNION ALL
        SELECT 
            ateam Team, 
            1 P, 
            IF(hscore < ascore,1,0) W, 
            IF(hscore = ascore,1,0) D, 
            IF(hscore > ascore,1,0) L, 
            ascore F, 
            hscore A, 
            ascore-hscore GD, 
            CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS 
        FROM games
    ) as tot 
    JOIN teams t ON tot.Team=t.id 
    GROUP BY t.id, t.tname
    ORDER BY SUM(tot.Pts) DESC, SUM(tot.GD) DESC, SUM(tot.F) DESC
) AS ranked_teams
-- 这里要更新变量，确保下一行能拿到当前行的成绩
CROSS JOIN (SELECT @row_num := 0, @rank := 0, @prev_points := NULL, @prev_gd := NULL, @prev_f := NULL) AS vars;

这个版本里，如果当前球队的积分、净胜球、进球数和上一支完全一致，就会和上一支共享同一个排名；如果不一样，就用当前的行号作为新的排名。

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