时间戳

首页 > 解决方案 > 使用 group by 在两个表中获取最大值时遇到问题

mysql - 使用 group by 在两个表中获取最大值时遇到问题

问题描述

我一直在尝试解决一个问题很长一段时间——几天——但我没有取得任何进展。基本上，我有两张桌子，players和matches. 每个玩家players都有一个唯一的player_id，以及一个group_id标识他/她属于哪个组的 。每场比赛中matches都有player_id两名球员的 sfirst_player和second_player，他们总是来自同一组。first_score对应打分的first_player分数，second_score对应打分的second_player分数。得分多的人赢得比赛。下面是两张表：

create table players (
      player_id integer not null unique,
      group_id integer not null
);

create table matches (
      match_id integer not null unique,
      first_player integer not null,
      second_player integer not null,
      first_score integer not null,
      second_score integer not null
);

现在我要做的是让每个组中获胜次数最多的玩家、他们的组 ID 以及获胜次数。因此，例如，如果有三个组，结果将类似于：

Group    Player    Wins
1        24        23
2        13        25
3        34        20

这是我现在拥有的

SELECT p1.group_id AS Group, p1.player_id AS Player, COUNT(*) AS Wins
FROM players p1, matches m1
WHERE (m1.first_player = p1.player_id AND m1.first_score > m1.second_score) 
   OR (m1.second_player = p1.player_id AND m1.second_score > m1.first_score)
GROUP BY p1.group_id
HAVING COUNT(*) >= (
            SELECT COUNT(*)
            FROM players p2, matches m2
            WHERE p2.group_id = p1.group_id AND
                ((m2.first_player = p2.player_id AND m2.first_score > m2.second_score) 
                   OR (m2.second_player = p2.player_id AND m2.second_score > m2.first_score))
)

我的想法是只选择获胜次数大于或等于他组中所有其他玩家获胜次数的玩家。我的查询存在一些语法问题。我想我也错误地使用了 GROUP BY。

还有一个获胜次数并列的问题，我应该只得到最少的玩家player_id。但我什至还没有达到那个地步。非常感谢您的帮助，谢谢！

编辑 1

我有一些正在运行查询的示例数据。

SELECT * FROM players给了我这个：

Player_ID Group_ID
100        1
200        1
300        1
400        2
500        2
600        3
700        3

SELECT * FROM matches给了我这个：

match_id first_player second_player first_score second_score
1        100          200           10          20
2        200          300           30          20
3        400          500           30          10
4        500          400           20          20
5        600          700           20          10

所以，查询应该返回：

Group    Player    Wins
1        200       2
2        400       1
3        600       1

按原样运行查询会返回以下错误：

ERROR:  column "p1.player_id" must appear in the GROUP BY clause or be used in an aggregate function

现在我明白了，player_id如果我想在 SELECT（或 HAVING）语句中使用它，我必须在 GROUP BY 子句中指定，但我不希望按玩家 ID 分组，只按组 ID。

即使我在外部查询中将 p1.player_id 添加到 GROUP BY 中，我也得到了......实际上是正确的答案。但我有点困惑。Group By 不会根据该列聚合表吗？从逻辑上讲，我只想按 p1.group_id 分组。

另外，如果我要在一个组中有多个获胜次数最多的玩家，我如何只保留获胜次数最少的那个player_id？

编辑 2

如果我将matches表格更改为第 1 组，有两名玩家每人赢得 1 场胜利，则查询结果将完全从结果中省略第 1 组。所以，如果我的matches桌子是：

match_id first_player second_player first_score second_score
1        100          200           10          20
2        200          300           10*         20
3        400          500           30          10
4        500          400           20          20
5        600          700           20          10

我希望结果是

Group    Player    Wins
1        200       1
1        300       1
2        400       1
3        600       1

但是，我得到以下信息：

Group    Player    Wins
2        400       1
3        600       1

请注意，所需的结果是

Group    Player    Wins
1        200       1
2        400       1
3        600       1

因为我希望player_id在平局的情况下只选择最少的玩家。

标签： mysqlsqlpostgresql

解决方案

WITH first_players AS (
    SELECT group_id,player_id,SUM(first_score) AS scores FROM players p LEFT JOIN matches m ON p.player_id=m.first_player GROUP BY group_id,player_id
    ),
    second_players AS (
    SELECT group_id,player_id,SUM(second_score) AS scores FROM players p LEFT JOIN matches m ON p.player_id=m.second_player GROUP BY group_id,player_id
    ),
    all_players AS (
        WITH al AS (
            SELECT group_id, player_id, scores FROM first_players 
            UNION ALL
            SELECT group_id, player_id, scores FROM second_players
        )
        SELECT group_id, player_id,COALESCE(SUM(scores),0) AS scores FROM al GROUP BY group_id, player_id 
    ),
    players_rank AS (
        SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY scores DESC, player_id ASC) AS score_rank,
        ROW_NUMBER() OVER(PARTITION BY scores ORDER BY player_id ASC) AS id_rank FROM all_players ORDER BY group_id
    )
    SELECT group_id, player_id AS winner_id FROM players_rank WHERE score_rank=1 AND id_rank=1

结果

   group_id winner_id
    1   45
    2   20
    3   40

试试看

推荐阅读