MySQL 查询联盟脚本表

Question

我目前正在为联盟脚本制作表格。这些是我的 mysql 表:https://pastebin.com/raw/iuNbzGTS这是我当前的 SQL 查询:

SELECT
   name AS Team, Sum(P) AS P,Sum(W) AS W,Sum(L) AS L,SUM(Pts) AS Pts
FROM(
  SELECT
    teamid1 Team,
    1 P,
    IF(team1score > team2score,1,0) W,
    IF(team1score < team2score,1,0) L,
    CASE WHEN team1score > team2score THEN 1 WHEN team1score = team2score THEN 1 ELSE 0 END PTS
  FROM games
  UNION ALL
  SELECT
    teamid2,
    1,
    IF(team1score < team2score,1,0),
    IF(team1score > team2score,1,0),
    CASE WHEN team1score < team2score THEN 1 WHEN team1score = team2score THEN 1 ELSE 0 END
  FROM games
) as tot
JOIN teams t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC ;

问题是它只显示已经打过比赛的球队。 SQL 查询不返回其他团队。我想返回尚未参加比赛的表格底部的球队。我该怎么做？

谢谢谨致问候

Answer 1

使用左连接:

FROM    teams t 
LEFT JOIN
        (
        ... your subquery here ...
        ) as tot
ON      tot.Team = t.id