mysql - 连接两个表两次

Question

写一个篮球数据库的查询，我们的游戏表有一个 winnerID 和一个 loserID，每个都是一个 teamID。尝试了以下两个查询，每个查询都正确地给出了获胜次数，但给出了相同的失败次数。

SELECT team.name as Team_Name, COUNT(team.teamID=winner.winnerID) as Wins, COUNT(team.teamID=loser.loserID) as Losses
FROM team join games winner on winner.winnerID=team.teamID join games loser on loser.loserID=team.teamID
GROUP BY team.name
ORDER BY Wins, Team_Name;

SELECT team.name as Team_Name, COUNT(team.teamID=games.winnerID) as Wins, COUNT(team.teamID=games.loserID) as Losses
FROM (team INNER JOIN games on games.winnerID=team.teamID)
GROUP BY team.name
ORDER BY Wins, team.name;

帮忙吗？

编辑:忘记说了，查询的目的是获取每支球队的胜负数。

Answer 1

COUNT 聚合获取非 NULL 值的计数。这意味着它包括 1 和 0。

在数字上下文中计算，相等比较返回 1 为 TRUE，返回 0 为 FALSE，并且只返回 NULL 如果任何一方(或双方)为 NULL。

要将 1 相加并忽略 0，您可以改用 SUM 聚合。

由于 winner 和 loser 之间的交叉连接，查询的一个大问题是可能返回重复项。如果一支球队赢了 5 场，输了 4 场，查询将生成一个包含 20 (= 5 x 4) 行的中间集。

要从中计算输赢，我们需要一个游戏的唯一标识符(例如，游戏表中的 gameid 列是 PRIMARY KEY。)有了它，我们就可以得到一个计数gameid 的不同值。例如:

  COUNT(DISTINCT IF(winner.winnerid=team.teamid,winner.gameid,NULL)) AS wins  

---

有几种查询模式可以让您了解每支球队的获胜次数和失败次数。

举个例子:

 SELECT t.name                                AS team_name
      , COUNT(IF(t.teamid=g.winnerid,1,NULL)  AS wins
      , COUNT(IF(t.teamid=g.loserid ,1,NULL)  AS losses
   FROM team
   LEFT
   JOIN games g
     ON ( g.winnerid = t.teamid OR g.loserid = t.teamid )
  GROUP
     BY t.name
  ORDER
     BY wins DESC
      , t.name

有了这个，我们只加入游戏 table 一次，所以我们不会得到叉积。另请注意，如果 teamid 不等于 winnerid，我们将返回 NULL 而不是 0。因此 COUNT 将仅包括获胜者，而不是所有行。

我们使用外连接(而不是内连接)，以防团队的游戏中没有相关行。这使我们能够返回计数为零的团队。

我们可以使用 SUM 聚合而不是 COUNT，例如:

 SELECT t.name                              AS team_name
      , IFNULL(SUM(t.teamid=g.winnerid),0)  AS wins
      , IFNULL(SUM(t.teamid=g.loserid) ,0)  AS losses
   FROM team
   LEFT
   JOIN games g
     ON ( g.winnerid = t.teamid OR g.loserid = t.teamid )
  GROUP
     BY t.name
  ORDER
     BY wins DESC
      , t.name

使用 SUM() 我们有可能返回 NULL 值。为了将它们转换为零，我们使用 IFNULL 函数。

还有其他几种查询模式可以为您提供相同的结果...例如不要使用连接，而是在 SELECT 列表中使用相关的子查询...

SELECT t.name 
     , ( SELECT COUNT(1)
           FROM game w
          WHERE w.winner_id = t.teamid
       ) AS wins
     , ( SELECT COUNT(1)
           FROM game l
          WHERE l.loserid = t.teamid
       ) AS losses
  FROM team t
 ORDER BY wins DESC
     , t.name