mysql - GROUP BY 的求和字段失败

Question

我有一个非常讨厌的问题。我尝试获得按达阵次数排序的分类表...但在达到该目标之前，我遇到了求和问题...让我解释一下。

使用此查询:

SELECT tft.id_team
     , z.id_partido
     , y.td 
  FROM `team_faseTorneo` tft
  LEFT 
  JOIN 
     ( SELECT j.id_fase
            , tp.id_team
            , tp.id_partido 
         FROM jornadas j 
         LEFT 
         JOIN partidos USING(id_jornada) 
         JOIN team_partido tp USING(id_partido)
     ) z 
    ON z.id_fase = tft.id_fase 
   AND z.id_team=tft.id_team
  LEFT 
  JOIN 
     ( SELECT p.id_team
            , pL.id_partido
            , pL.td 
         FROM players p 
         JOIN playerLogros pL USING(id_player)
     ) y 
    ON y.id_team = tft.id_team 
   AND y.id_partido = z.id_partido
 WHERE tft.id_fase = 4

结果是这样的:

id_team id_partido  td
6   69  0
6   69  0
6   69  0
6   69  0
6   81  0
6   81  0
6   81  0
6   81  1
6   84  1
6   84  0
6   98  NULL
6   99  NULL
6   114 NULL
6   116 NULL
6   129 NULL
6   133 NULL
6   144 NULL
6   150 NULL
6   159 NULL
6   167 NULL
19  72  0
19  72  0
19  77  2
19  77  0
19  77  1
19  77  0
19  77  0
19  89  0
19  89  0
19  92  NULL

NUll 表示尚未进行的比赛(partidos)。我需要将比赛(partido)的所有达阵得分相加，所以我尝试了以下方法:

SELECT tft.id_team,z.id_partido, y.td FROM `team_faseTorneo` tft
 LEFT JOIN (SELECT j.id_fase, tp.id_team, tp.id_partido FROM jornadas j LEFT JOIN partidos USING(id_jornada) JOIN team_partido tp USING(id_partido)
) z ON(z.id_fase=tft.id_fase AND z.id_team=tft.id_team) LEFT JOIN(SELECT p.id_team, pL.id_partido, SUM(pL.td) td FROM players p JOIN playerLogros pL USING(id_player) GROUP BY p.id_team) y ON(y.id_team=tft.id_team AND y.id_partido=z.id_partido) 
WHERE tft.id_fase=4

但是结果是这样的:

id_team id_partido  td
6   69  NULL
6   81  NULL
6   84  NULL
6   98  NULL
6   99  NULL
6   114 NULL
6   116 NULL
6   129 NULL
6   133 NULL
6   144 NULL
6   150 NULL
6   159 NULL
6   167 NULL
19  72  3
19  77  NULL
19  89  NULL
19  92  NULL
19  106 NULL

我该如何解决这个问题？提前致谢!! :)

Answer 1

如果您需要总和，请使用聚合查询。也许是这样的:

SELECT tft.id_team, z.id_partido, SUM(y.td)
FROM `team_faseTorneo` tft LEFT JOIN
      (SELECT j.id_fase, tp.id_team, tp.id_partido
       FROM jornadas j LEFT JOIN
            partidos
            USING (id_jornada) JOIN
            team_partido tp
            USING (id_partido)
      ) z
      ON z.id_fase=tft.id_fase AND z.id_team=tft.id_team LEFT JOIN
      (SELECT p.id_team, pL.id_partido, pL.td td
       FROM players p JOIN
            playerLogros pL
            USING (id_player)
      ) y
      ON y.id_team=tft.id_team AND y.id_partido=z.id_partido
WHERE tft.id_fase = 4
GROUP BY tft.id_team, z.id_partido;

您的查询似乎过于复杂，但这至少应该得到 td 的总和。