mysql - 在体育表中将主客场结果合并在一起

Question

我在单独的查询中拥有主场和客场结果的代码，但我不确定如何将其连接在一起以同时获得主场和客场结果。

代码首页体育联盟

SELECT
teamName AS Team, 
 Sum(P) AS P,
Sum(W) AS W,
Sum(D) AS D,
 Sum(L) AS L,
SUM(F) as F,
SUM(A) AS A,
SUM(GD) AS GD,
SUM(Pts) AS Pts
  FROM(
  SELECT
  teamHomeID Team,
  1 P,
  IF(matchScoreHome > matchScoreAway,1,0) W,
  IF(matchScoreHome = matchScoreAway,1,0) D,
  IF(matchScoreHome < matchScoreAway,1,0) L,
  matchScoreHome F,
  matchScoreAway A,
  matchScoreHome-matchScoreAway GD,
  CASE WHEN matchScoreHome > matchScoreAway THEN 3 WHEN matchScoreHome =    matchScoreAway THEN 1 ELSE 0 END PTS
 FROM tblmatches WHERE matchstatus = 'FT' AND competitionID = 1 AND seasonID = 2     
) as tot
 JOIN tblteam t ON tot.team=t.teamID
 GROUP BY Team
 ORDER BY SUM(Pts) DESC ;

CODE AWAY 体育联盟

 SELECT
 teamName AS Team, 
 Sum(P) AS P,
 Sum(W) AS W,
 Sum(D) AS D,
 Sum(L) AS L,
 SUM(F) as F,
 SUM(A) AS A,
 SUM(GD) AS GD,
 SUM(Pts) AS Pts
 FROM(
 SELECT
 teamAwayID Team,
  1 P,
  IF(matchScoreHome < matchScoreAway,1,0) W,
  IF(matchScoreHome= matchScoreAway,1,0) D,
  IF(matchScoreHome > matchScoreAway,1,0) L,
  matchScoreAway F,
  matchScoreHome A,
  matchScoreAway-matchScoreHome GD,
 CASE WHEN matchScoreHome < matchScoreAway THEN 3 WHEN matchScoreHome = matchScoreAway THEN 1 ELSE 0 END PTS
 FROM tblmatches WHERE matchstatus = 'FT' AND competitionID = 1 AND seasonID = 2
) as tot
 JOIN tblteam t ON tot.team=t.teamID
 GROUP BY Team
 ORDER BY SUM(Pts) DESC ;

每个表如下所示

 --------------------------------------------------
 ¦ Team Name ¦ P ¦ W ¦ D ¦ L ¦ F  ¦ A  ¦ GD ¦ Pts ¦
 ¦ Man City  ¦ 8 ¦ 6 ¦ 0 ¦ 2 ¦ 23 ¦ 10 ¦ 13 ¦ 18  ¦
 ¦ Tottehham ¦ 8 ¦ 4 ¦ 4 ¦ 0 ¦ 14 ¦ 5  ¦ 9  ¦ 16  ¦
 --------------------------------------------------

我想将两个表连接在一起以获得如下所示的内容:

     -----------------------------------------------------------------------------
 Team Name ¦ P ¦ W ¦ D ¦ L ¦ F ¦ A ¦ GD ¦ HW ¦ HD ¦ HL ¦ HF ¦ HA ¦ AW ¦ AD ¦ AL ¦ PTS ¦

Answer 1

快速解决方法是将“tot”重命名为“tothome”和“totaway”，然后您就可以或多或少地合并查询。

但是，您必须引用例如F as totaway.F 获取球队在客场比赛中的进球数。

您将在 SUM(P) 中得到 P 的歧义。在那里你必须 SUM(tothome.P) + SUM(totaway.P) 才能得到 P。