如何在MySQL中从一对多关联表中查询每个项目的最新笔记？

我有两张表：Project和Projectnote，二者是一对多的关联关系。我想列出所有项目，并基于id字段获取每个项目对应的最新Projectnote记录。请问能不能用MySQL查询实现这个需求？目前我写了下面的基础关联查询，但这个查询只能筛选出存在笔记的项目，而且当一个项目有多条笔记时会返回多行重复记录：

SELECT `driver_checkins`.*, `driver_trips`.`id` AS `trip_id`, `driver_trips`.`trip_num` AS `trip_num`, `driver_trips`.`status` AS `trip_status`, `driver_trips`.`ride_date` AS `ride_date`, `driver_trips`.`today_date` AS `trip_today_date`, `driver_trips`.`pick_up_time` AS `pick_up_time`, `driver_trips`.`d_time` AS `d_time`, `driver_trips`.`trip_type` AS `trip_type`
FROM `driver_checkins`
LEFT JOIN `driver_trips` ON `driver_trips`.`driver_id` = `driver_checkins`.`driver_id` WHERE `checkin_status` = 1 AND `booking_status` = 0;

可以实现，给你两种常用解决方案： #

方法一：子查询筛选最新笔记ID #

先通过子查询找出每个项目对应的最大Projectnote.id（假设id是自增主键，数值越大代表记录越新），再关联回Projectnote表拿到完整笔记数据，同时用LEFT JOIN确保所有项目都能被列出，哪怕没有笔记：

SELECT 
    p.*, 
    pn.*
FROM 
    Project p
LEFT JOIN (
    -- 按项目分组，取每个组里最大的笔记ID
    SELECT project_id, MAX(id) AS latest_note_id
    FROM Projectnote
    GROUP BY project_id
) latest_pn ON p.id = latest_pn.project_id
LEFT JOIN Projectnote pn ON latest_pn.latest_note_id = pn.id;

方法二：窗口函数（MySQL 8.0+适用） #

用ROW_NUMBER()窗口函数给每个项目下的笔记按id降序排号，然后只取排号为1的那条（也就是最新的笔记），这种方式更灵活，要是以后需要按其他字段（比如创建时间）排序，改ORDER BY就行：

SELECT 
    p.*, 
    pn.*
FROM 
    Project p
LEFT JOIN (
    SELECT 
        *,
        -- 按项目分组，每组内按id倒序排，给每条记录打序号
        ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY id DESC) AS rn
    FROM Projectnote
) pn ON p.id = pn.project_id AND pn.rn = 1;

针对你现有查询的说明 #

你当前的查询用了driver_checkins和driver_trips，如果对应到你的Project和Projectnote场景，核心思路是一致的：

• 必须用LEFT JOIN才能保留没有关联记录的项目
• 通过子查询或窗口函数过滤出每个主表记录对应的单条最新子表记录，避免返回重复行

内容的提问来源于stack exchange，提问作者Sivan