Início / dba / Perguntas / 305458
Accepted
Googlebot
Asked: 2021-12-29 18:59:56 +0800 CST

UPDATE uma tabela por self JOIN e GROUP BY

  • 772

Eu tenho uma tabela de jogadores da equipe em que PlayerIDestá faltando em algumas linhas.

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

Eu tento combinar o PlayerIDpara cada nome. No entanto, como os nomes não são únicos (duas pessoas podem ter o mesmo nome), devo confiar em algumas aproximações.

A primeira tentativa é assumir que os nomes dos co-jogadores são únicos. Por exemplo,

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

Assumindo que o Team Cook jogou com apenas uma pessoa chamada John West e vice-versa.

Saída esperada:

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

eu comecei com

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

mas qual é o caminho certo para UPDATEa mesa?

SQL Fiddle

mysql mariadb

1 respostas

  1. Best Answer

    Teste isso:

    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. Talvez a definição da janela precise estar correta em PARTITION BY ( PARTITION BY Name1, Name2, TeamID?) - mas a matriz de dados de amostra é muito pequena e não contém dados suficientes.

    • 1

relate perguntas