通過自我 JOIN 和 GROUP BY 更新表

我有一個團隊成員表，其中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小提琴

測試這個：

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?) - 但是樣本數據數組太小並且不包含足夠的數據。

引用自：https://dba.stackexchange.com/questions/305458