Mysql
通過自我 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
餐桌的正確方法是什麼?
測試這個:
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
?) - 但是樣本數據數組太小並且不包含足夠的數據。