mysql 为什么这个 SQL 语句 team_id 不能升序

Question

/////////////////////////////////////////////////////////////////////////////////////////

题目要求：

        查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id  升序排序

/////////////////////////////////////////////////////////////////////////////////////////

# Write your MySQL query statement below

SELECT
    t.team_id, t.team_name, IFNULL(num_points, 0) AS `num_points`
FROM
    teams AS `t` LEFT JOIN (
        SELECT team_id, SUM(points) AS `num_points`
        FROM
        (
            -- 算出主队每场比赛得分
            SELECT host_team AS `team_id`,
            (
                CASE
                    WHEN host_goals>guest_goals THEN 3
                    ELSE (CASE WHEN host_goals<guest_goals THEN 0 ELSE 1 END)
                END
            ) AS `points`
            FROM matches

            UNION ALL

            -- 算出客队每场比赛得分
            SELECT guest_team AS `team_id`,
            (
                CASE
                    WHEN guest_goals>host_goals THEN 3
                    ELSE (CASE WHEN guest_goals<host_goals THEN 0 ELSE 1 END)
                END
            ) AS `points`
            FROM matches
        ) AS `a`
        GROUP BY team_id
    ) AS `m` ON t.team_id=m.team_id
ORDER BY m.num_points DESC, t.team_id ASC

代码运行结果：

疑问：代码里面我写了“ORDER BY m.num_points DESC, t.team_id ASC” 但是为什么在积分相同的情况下没有按照 team_id 进行升序呢？

Answer 1

SELECT
    t.team_id, t.team_name, IFNULL(num_points, 0) AS `num_points`
FROM
    teams AS `t` LEFT JOIN (
        SELECT team_id, SUM(points) AS `num_points`
        FROM
        (
            -- 算出主队每场比赛得分
            SELECT host_team AS `team_id`,
            (
                CASE
                    WHEN host_goals>guest_goals THEN 3
                    ELSE (CASE WHEN host_goals<guest_goals THEN 0 ELSE 1 END)
                END
            ) AS `points`
            FROM matches
 
            UNION ALL
 
            -- 算出客队每场比赛得分
            SELECT guest_team AS `team_id`,
            (
                CASE
                    WHEN guest_goals>host_goals THEN 3
                    ELSE (CASE WHEN guest_goals<host_goals THEN 0 ELSE 1 END)
                END
            ) AS `points`
            FROM matches
        ) AS `a`
        GROUP BY team_id
    ) AS `m` ON t.team_id=m.team_id
ORDER BY 3 DESC, 1 ASC