主页 / dba / 问题 / 305458
Accepted
Googlebot
Asked: 2021-12-29 18:59:56 +0800 CST

通过自我 JOIN 和 GROUP BY 更新表

  • 772

我有一个团队成员表，其中PlayerID某些行缺少。

CREATE TABLE TeamPlayers
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
TeamID int(11) unsigned NOT NULL,
PlayerID int(11) unsigned,
Name varchar(255),
INDEX(PlayerID),
INDEX(Name),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

我尝试匹配PlayerID每个名称。然而，由于名字不是唯一的（两个人可以有相同的名字），我必须依赖一些近似值。

第一个尝试是假设合作者的名字是唯一的。例如，

ID    TeamID    PlayerID    Name
1     15        9           Tim Cook
2     15        NULL        John West
3     16        NULL        Tim Cook
4     16        5           John West
5     17        11          John West
6     18        NULL        John West

假设库克队只与一个名叫约翰·韦斯特的人一起比赛，反之亦然。

预期输出：

ID    TeamID    PlayerID    Name
1     15        9           Tim Cook
2     15        5           John West
3     16        9           Tim Cook
4     16        5           John West
5     17        11          John West
6     18        NULL        John West

我从

SELECT a.Name,MAX(a.PlayerID),MAX(b.PlayerID),
    GROUP_CONCAT(a.ID SEPARATOR ','),    GROUP_CONCAT(b.ID SEPARATOR ',') 
  FROM TeamPlayers a JOIN TeamPlayers b 
  ON a.TeamID=b.TeamID AND a.Name<>b.Name
  GROUP BY a.Name

但是到UPDATE餐桌的正确方法是什么？

SQL小提琴

mysql mariadb

1 个回答

  1. Best Answer

    测试这个：

    UPDATE TeamPlayers t1
JOIN ( SELECT LEAST(t1.Name, t2.Name) Name1, 
              MAX(CASE WHEN LEAST(t1.Name, t2.Name) = t1.Name
                       THEN t1.PlayerID
                       ELSE t2.PlayerID
                       END) OVER () ID1,
              GREATEST(t1.Name, t2.Name) Name2, 
              MAX(CASE WHEN GREATEST(t1.Name, t2.Name) = t1.Name
                       THEN t1.PlayerID
                       ELSE t2.PlayerID
                       END) OVER () ID2,
              TeamID
      FROM TeamPlayers t1
      JOIN TeamPlayers t2 USING (TeamID)
      WHERE t1.Name <> t2.Name
      GROUP BY Name1, Name2, TeamID ) t2 USING (TeamID)
SET t1.PlayerID = CASE WHEN t1.Name = t2.Name1
                       THEN t2.ID1
                       ELSE t2.ID2
                       END;

    https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=16f5b70672ce37a20ce120f29012a646

    PS。也许窗口定义需要正确的 PARTITION BY ( PARTITION BY Name1, Name2, TeamID?) - 但是样本数据数组太小并且不包含足够的数据。

    • 1

相关问题