mysql - 无法排除未 JOIN 的记录

Question

我只需要返回有排名的比赛，该排名位于表 league_ranking 内，每个比赛都与 round_id 列关联。

本质上，查询应该返回特定国家的所有比赛，条件是该国家的所有比赛必须有排名，所以我这样做了:

SELECT c.name AS competition_name,
  c.id AS competition_id
  FROM competition c
  JOIN competition_seasons s ON s.competition_id = c.id
  JOIN competition_rounds r ON r.season_id = s.id
  LEFT JOIN league_ranking l ON l.round_id = r.id
  WHERE c.country_id = :country_id AND l.round_id IS NULL
  GROUP BY c.id
  ORDER BY c.name ASC

此查询还返回没有任何排名的比赛，例如:

竞争

id | country_id | name
202     12         Cup

competition_seasons

id  | competition_id | name
955       202           2018/2019

competition_rounds

id    | season_id |
 2122    955

league_ranking

该轮没有记录

上面的查询也会返回竞赛202，我做错了什么？

Answer 1

您的查询目前是专门设计用于仅返回没有联赛排名的比赛，因为它使用 LEFT JOIN 到 league_ranking，然后检查l.round_id IS NULL，只有当比赛没有联赛排名时才会成立(即没有与l.round_id = r.id匹配的行)。如果您想要仅有联赛排名的比赛，请将 LEFT JOIN 更改为 JOIN 并删除 l.round_id IS NULL 条件。请注意，您不需要 GROUP BY 子句，我已将其删除。

SELECT c.name AS competition_name,
  c.id AS competition_id
  FROM competition c
  JOIN competition_seasons s ON s.competition_id = c.id
  JOIN competition_rounds r ON r.season_id = s.id
  JOIN league_ranking l ON l.round_id = r.id
  WHERE c.country_id = :country_id
  ORDER BY c.name ASC

Demo on dbfiddle