MYSQL-将最近匹配的记录从一个表连接到另一个表 [英] MYSQL - Join most recent matching record from one table to another

问题描述

我有两个看起来像这样的表:

I have two tables that look like this:

表:案例

id
名称
状态
case_no

id
name
status
case_no

表:注释

id
case_id
note_date
笔记

id
case_id
note_date
notes

我希望能够创建一个查询，该查询从case表中获取数据，并且仅从notes表中获取case表中每一行的最新条目.到目前为止，我根本没有运气.

I'd like to be able to create a query that grabs the data from the cases table and only the most recent entry from the notes table for each row in the cases table. So far I'm having no luck at all.

任何指针将不胜感激

推荐答案

这将仅返回带有注释的案例:

This will return only the cases with notes attached:

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

如果您希望所有情况，无论它们是否附带便条:

If you want all cases, regardless if they have a note attached:

   SELECT c.*,
          x.*
     FROM CASES c
LEFT 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

这篇关于MYSQL-将最近匹配的记录从一个表连接到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！