MySQL多表关联查询：获取每个m_article对应id_m_process=8的最新process_article记录

问题原因 #

你现有SQL存在两个问题：

• 表名拼写错误：你写的m_proses_detail实际应为m_process_detail
• 未实现「按文章分组取每组最大id_m_pa对应记录」的逻辑，现有SQL会返回所有满足id_m_process='8'的关联记录，自然不是你要的结果

正确SQL写法 #

提供两种常用实现方案，可根据你使用的数据库版本选择：

方案1：窗口函数实现（支持MySQL8.0+/PostgreSQL/Oracle等所有支持窗口函数的数据库）

SELECT code, id_mpd
FROM (
    SELECT 
        ma.code, 
        pa.id_mpd,
        ROW_NUMBER() OVER(PARTITION BY ma.id_m_article ORDER BY pa.id_m_pa DESC) AS rn
    FROM m_article ma 
    JOIN process_article pa ON ma.id_m_article = pa.id_m_article 
    JOIN m_process_detail mpd ON pa.id_mpd = mpd.id_mpd 
    WHERE mpd.id_m_process = '8'
) t
WHERE rn = 1;

方案2：子查询关联实现（兼容低版本MySQL等不支持窗口函数的数据库）

SELECT ma.code, pa.id_mpd
FROM m_article ma
JOIN process_article pa ON ma.id_m_article = pa.id_m_article
JOIN (
    SELECT id_m_article, MAX(id_m_pa) AS max_pa_id
    FROM process_article pa
    JOIN m_process_detail mpd ON pa.id_mpd = mpd.id_mpd
    WHERE mpd.id_m_process = '8'
    GROUP BY id_m_article
) t ON pa.id_m_pa = t.max_pa_id AND pa.id_m_article = t.id_m_article

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