本文介绍了左加入最新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个桌子

游戏和比分:

游戏具有主键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

几乎可以正常工作,它会在每个游戏的得分表中获取最新的条目.但是,它只会返回得分表中具有相应条目的游戏.我需要它返回表中的所有游戏,无论它们是否在得分表中都有条目.通过阅读前面提到的问题,我认为左联接将完成.

it almost works, it gets the most recent entry in the score table for each game. however it only returns games which have a corresponding entry in the score table. and i need it to return all games in the table reguardless of if they have a entry in the score table. which from reading the previously cited question i assumed that the left join would accomplish.

推荐答案

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

这篇关于左加入最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

查看更多