本文介绍了左加入最新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个桌子
游戏和比分:
游戏具有主键ID
得分具有game_id,该game_id引用game.id并在其他字段中创建.
Scores has game_id which references games.id and created on among other fields.
我正在尝试采用此.
i am attempting to adapt the second example in the accepted answer of this question to fit my needs.
SELECT g.*,
s.*
FROM GAMES g
LEFT JOIN SCORES s ON s.game_id = g.id
JOIN (SELECT n.game_id,
MAX(n.created_on) AS max_score_date
FROM SCORES n
GROUP BY n.game_id) y ON y.game_id = s.game_id
AND y.max_score_date = s.created_on
推荐答案
SELECT
g.*, s.*
FROM
GAMES g
LEFT JOIN
(
SCORES s
INNER JOIN
(
SELECT
n.game_id, MAX(n.created_on) AS max_score_date
FROM
SCORES n
GROUP BY
n.game_id
)
y
ON y.game_id = s.game_id
AND y.max_score_date = s.created_on
)
ON s.game_id = g.id
这篇关于左加入最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!