sql - GROUP BY 子句不返回每个组 ID 中的获胜者

Question

我试图返回每个组 id 中的获胜者玩家，但是，按功能分组似乎不起作用。将感谢您对此的投入。

SELECT
    group_id,
    player_id AS winner_id
FROM players AS p
    LEFT JOIN (
        SELECT
            sum(first_score) AS total_first,
            first_player
        FROM matches
        GROUP BY first_player
        ) AS a
        ON p.player_id = a.first_player
    LEFT JOIN (
        SELECT
            sum(second_score) as total_second,
            second_player
        FROM matches
        GROUP BY second_player
        ) AS b
        ON p.player_id = b.second_player
    GROUP BY p.group_id
    ORDER BY
        coalesce(total_first, 0) + coalesce(total_second, 0) DESC,
        player_id ASC

Answer 1

在您的查询中没有聚合函数，所以不需要 group by 只需使用 distinct

select distinct group_id, player_id as winner_id
from players as p
left join 
(SELECT sum(first_score) as total_first, first_player
from matches
group by first_player) as a
on p.player_id = a.first_player
left join 
(SELECT sum(second_score) as total_second, second_player
from matches
group by second_player) as b
on p.player_id = b.second_player    
order by coalesce(total_first,0)+coalesce(total_second,0) desc, player_id asc