MySQL关联两表查询如何获取每个项目最新date_created对应的评论数据

关联project_list与user_productivity表，查询结果按project_list.id去重，每个项目仅返回1条记录，comment字段取该项目所有关联user_productivity记录中date_created最新的对应值，无关联记录的项目对应字段留空。

• project_list表：存储项目基础信息，字段包含id、name
• user_productivity表：存储项目任务的用户反馈及创建时间，字段包含id、project_id、task_id、comment、date_created
• task_list表：存储任务与项目的关联关系，字段包含id、project_id

方案1：GROUP BY分组去重 #

SELECT project_list.id, project_list.name, user_productivity.project_id, 
        user_productivity.comment, user_productivity.date_created 
FROM project_list 
    LEFT JOIN user_productivity ON project_list.id = user_productivity.project_id 
GROUP BY project_list.id

问题：可实现按项目id去重，但返回的comment不是最新date_created对应的记录。

方案2：按创建时间降序排序 #

SELECT project_list.id, project_list.name, user_productivity.project_id, 
        user_productivity.comment, user_productivity.date_created 
FROM project_list 
    LEFT JOIN user_productivity ON project_list.id = user_productivity.project_id 
ORDER BY unix_timestamp(user_productivity.date_created) desc

问题：可返回最新的comment记录，但同一个项目会返回多条关联记录，未实现去重。

方案1：MySQL 8.0+ 窗口函数实现（推荐） #

WITH ranked_feedback AS (
    SELECT 
        up.project_id,
        up.comment,
        up.date_created,
        ROW_NUMBER() OVER (PARTITION BY up.project_id ORDER BY up.date_created DESC) AS rn
    FROM user_productivity up
)
SELECT 
    pl.id AS project_id,
    pl.name AS project_name,
    rf.comment,
    rf.date_created
FROM project_list pl
LEFT JOIN ranked_feedback rf ON pl.id = rf.project_id AND rf.rn = 1;

说明：先对每个项目的用户反馈按创建时间倒序编号，关联时仅取每个项目编号为1的最新反馈记录，无反馈的项目对应字段自动留空，性能更优。

方案2：兼容低版本MySQL实现 #

SELECT 
    pl.id AS project_id,
    pl.name AS project_name,
    up.comment,
    up.date_created
FROM project_list pl
LEFT JOIN user_productivity up ON pl.id = up.project_id
LEFT JOIN (
    SELECT project_id, MAX(date_created) AS max_date
    FROM user_productivity
    GROUP BY project_id
) latest_up ON up.project_id = latest_up.project_id AND up.date_created = latest_up.max_date
GROUP BY pl.id;

说明：先通过子查询获取每个项目的最新反馈时间，再关联原反馈表获取对应时间的评论内容，最终按项目id去重，兼容MySQL 5.x等低版本环境。

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