SQL查询，如何改进？

mark 发表于 Dev

标记

我做了一个编写 SQL 查询的任务，我想知道我是否可以以某种方式改进它。

描述：

假设我们在某个在线服务上有一个数据库

让我们创建表，并插入一些数据

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
);

insert into players values(20, 2);
insert into players values(30, 1);
insert into players values(40, 3);
insert into players values(45, 1);
insert into players values(50, 2);
insert into players values(65, 1);
insert into matches values(1, 30, 45, 10, 12);
insert into matches values(2, 20, 50, 5, 5);
insert into matches values(13, 65, 45, 10, 10);
insert into matches values(5, 30, 65, 3, 15);
insert into matches values(42, 45, 65, 8, 4);

查询的输出应该是：

group_id | winner_id
--------------------
1        | 45
2        | 20
3        | 40

所以，我们应该输出每组的获胜者（玩家 ID）。获胜者是在比赛中获得最大积分的玩家。

如果用户独自在组中 - 他自动成为获胜者，如果玩家拥有相同的积分 - 获胜者是具有较低 id 值的人。

输出应按 group_id 字段排序

我的解决方案：

SELECT 
  results.group_id, 
  results.winner_id 
FROM 
  (
    SELECT 
      summed.group_id, 
      summed.player_id AS winner_id, 
      MAX(summed.sum) AS total_score 
    FROM 
      (
        SELECT 
          mapped.player_id, 
          mapped.group_id, 
          SUM(mapped.points) AS sum 
        FROM 
          (
            SELECT 
              p.player_id, 
              p.group_id, 
              CASE WHEN p.player_id = m.first_player THEN m.first_score WHEN p.player_id = m.second_player THEN m.second_score ELSE 0 END AS points 
            FROM 
              players AS p 
              LEFT JOIN matches AS m ON p.player_id = m.first_player 
              OR p.player_id = m.second_player
          ) AS mapped 
        GROUP BY 
          mapped.player_id
      ) as summed 
    GROUP BY 
      summed.group_id 
    ORDER BY 
      summed.group_id
  ) AS results;

它有效，但我 99% 确信它可以更清洁。将不胜感激任何建议

去世

首先，用于UNION ALL从matches2 列中提取：player_id和score所有玩家及其分数。
然后汇总得到每个玩家的总分。
最后对您获得的结果集进行LEFT连接，players用于GROUP_CONCAT()按总分降序收集每个组的所有玩家，并SUBSTRING_INDEX()选择第一个玩家：

SELECT p.group_id, 
       SUBSTRING_INDEX(GROUP_CONCAT(p.player_id ORDER BY t.score DESC, p.player_id), ',', 1) winner_id
FROM players p
LEFT JOIN (
  SELECT player_id, SUM(score) score
  FROM (
    SELECT first_player player_id, first_score score FROM matches
    UNION ALL
    SELECT second_player, second_score FROM matches
  ) t  
  GROUP BY player_id
) t ON t.player_id = p.player_id  
GROUP BY p.group_id;

请参阅演示。

请注意，通过进行LEFT连接，您将获得所有组的结果，即使是那些没有任何玩家参加任何比赛的组（就像您的样本数据一样），在这种情况下，获胜者是任意玩家（就像您的预期结果）。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。