我需要从表competition_rounds
中选择最新添加的记录,这些记录通过season_id
列链接到competition_seasons
表,例如:
competition_rounds
id | name | season_id
1 Round A 20
2 Round B 20
3 Round A 21
4 Round B 21
competition_seasons
id | name | competition_id
20 2017/2018 40
21 2018/2019 40
我只想退货:
round_id | round_name | season_id | season_name
3 Round A 21 2018/2019
4 Round B 21 2018/2019
问题是我的查询返回了所有可用回合:
$sql = $this->db->prepare("SELECT max(r.id) AS round_id,
r.name as round_name, r.season_id AS season_id, s.name AS season_name
FROM competition_rounds r
JOIN competition_seasons s ON r.season_id = s.id
JOIN competition c ON s.competition_id = c.id
WHERE c.id = :competition_id
GROUP BY r.id
ORDER BY max(r.season_id) DESC");
$sql->bindParam("competition_id", 40);
$sql->execute();
$rounds = $sql->fetchAll();
return $response->withJson($rounds);
注意:表competition
只是包含一个竞争参考的列表。
如果我理解正确,您可以使用子查询只返回季节表中的最新季节:
SELECT r.id AS round_id,
r.name as round_name, r.season_id AS season_id, s.name AS season_name
FROM competition_rounds r JOIN
(SELECT s.*
FROM competition_seasons s
WHERE s.competition_id = :competition_id
ORDER BY s.id DESC
LIMIT 1
) s
ON r.season_id = s.id ;
您的问题只提到两个表,尽管查询有三个表。这只是基于这个问题。
SELECT
中没有任何competition
列,因此可以从查询中省略它。对于给定的:competition_id
,取最后添加的(max(id)
(季节
SELECT r.id AS round_id,
r.name as round_name, r.season_id AS season_id, s.name AS season_name
FROM competition_rounds r
JOIN competition_seasons s ON r.season_id = s.id
WHERE s.id = (SELECT max(cs.id)
FROM competition_seasons cs
WHERE cs.competition_id = :competition_id)