mysql - 涉及多个 JOIN 的查询困难

Question

我的 table :

• 玩家(id_player、id_team)
• playerLogros (id_logro, id_player, TD, id_match) TD 是球员得分的达阵次数
• 匹配 (id_match, status) 状态说明游戏是否已结束
• team_match(id_team、id_match、tipo) tipo:本地/Visitante

我需要知道一支球队赢了多少场比赛。这取决于比赛中两队的 TD 得分。

我无法更改数据库的结构。我可以获取一支球队的 TD 得分，但同时，我不知道如何获取另一支球队的 TD 得分。

我当前的查询:

SELECT SUM(playerLogros.TD)
FROM playerLogros
LEFT JOIN players ON players.id_player=playerLogros.id_player
LEFT JOIN team_match ON team_match.id_team=players.id_team
LEFT JOIN matchs ON matchs.id_match = team_match.id_match
WHERE matchs.status='ended'
GROUP BY... 

这里有一些示例:

INSERT INTO players (id_player, id_team) VALUES 
(1,1), (2,1), (3,1), (4,1), 
(5,1), (6,1), (7,1), (8,1), 
(9,1), (10,1), (11,1), (12,2), 
(13,2), (14,2), (15,2), (16,2), 
(17,2), (18,2), (19,2), (20,2), 
(21,2), (22,2), (23,4), (24,4), 
(25,4), (26,4), (27,4), (28,4), 
(29,4), (30,4), (31,4), (32,4), 
(33,4), (34,5), (35,5), (36,5), 
(37,5), (38,5), (39,5), (40,5), 
(41,5), (42,5), (43,5), (44,5);

INSERT INTO  playerLogros (id_logro, id_player, TD, id_match) VALUES
(1,2,1,1), (2,10,1,1), (3,15,1,1), (4,29,2,2), (5,38,1,2), 
(6,6,1,3), (7,32,1,3), (8,16,1,4), (9,1,1,5), (10,42,1,5), (11,35,1,5),
(12,25,3,6), (13,28,1,6), (14,15,1,6), (15,29,1,6);

INSERT INTO matchs (id_match, status) VALUES
(1,'ended'),(2,'ended'),(3,'ended'),(4,'ended'),(5,'ended'),(6,'ended');

INSERT INTO team_match (id_team, id_match, tipo) VALUES 
(1,1,'Local'),(2,1,'Visitant'),(4,2,'Local'),(5,2,'Visitant'),
(1,3,'Local'),(4,3,'Visitant'),(2,4,'Local'),(5,4,'Visitant'),
(1,5,'Local'),(5,5,'Visitant'),(4,6,'Local'),(2,6,'Visitant');

查询应该给出如下内容:

id_team...Matches_won...Matches_tied...Matches_lost

...1.................1..................1.. ...................................1

...2.................1..................0.. ......................2

...4.................2.................................1.. ......................0

...5.................1..................0.. ......................2

Answer 1

这应该可以帮到你。预查询对每支球队进行聚合(基于球员与给定比赛得分的关联)并按每支球队和比赛组合进行分组。所以最后，您将获得 2 条比赛记录，每支球队各一条。

从中，返回到比赛表以查找相应的本地与访客球队 ID 并获取各自的值...按比赛分组，以便每场比赛汇总到单行。

SELECT 
      PreSumTDs.id_match,
      MAX( IF( tm.tipo = 'Local', tm.id_team, 0 )) as LocalTeam,
      SUM( IF( tm.tipo = 'Local' and tm.id_team = PreSumTDs.id_team, PreSumTDs.TeamTDs, 0 )) as LocalScore,
      MAX( IF( tm.tipo = 'Visitante', tm.id_team, 0 )) as VisitTeam,
      SUM( IF( tm.tipo = 'Visitante' and tm.id_team = PreSumTDs.id_team, PreSumTDs.TeamTDs, 0 )) as VisitScore
   from 
      ( select 
              pl.id_match,
              p.id_team,
              SUM( pl.TD ) as TeamTDs
           from
              matchs m
                 JOIN playerLogros pl
                    ON m.match_id = pl.match_id
                 JOIN players p
                    ON pl.id_player = p.id_player
           where
              m.status = 'ended'
           group by
              pl.id_match,
              p.id_team ) PreSumTDs
      JOIN team_match tm
         ON PreSumTDs.id_match = tm.id_match
   group by
      tm.id_match