MYSQL-将最近匹配的记录从一个表连接到另一个表

这只会返回带有注释的案例：

SELECt c.*,       x.*  FROM CASES c  JOIN NOTES x ON x.case_id = c.case_id  JOIN (SELECt n.case_id,    MAX(n.note_date) AS max_note_date          FROM NOTES n      GROUP BY n.case_id) y ON y.case_id = x.case_id     AND y.max_note_date = x.note_date

如果需要所有案例，无论是否附带注释：

   SELECt c.*,          x.*     FROM CASES cLEFT JOIN NOTES x ON x.case_id = c.case_id     JOIN (SELECt n.case_id,       MAX(n.note_date) AS max_note_date  FROM NOTES n         GROUP BY n.case_id) y ON y.case_id = x.case_id        AND y.max_note_date = x.note_date