如何编写MySQL联表查询语句获取三张表的指定组合记录？

现有表结构及数据 #

tb_injectiontemp表 #

tb_injection表 #

tb_notify_esim表 #

期望查询结果 #

原SQL问题分析 #

原SQL存在三个核心问题：

1. 关联条件不足：仅通过knackid关联tb_injectiontemp和tb_injection会产生笛卡尔积，同一个knackid下两张表都有多条记录，无法正确一一匹配。
2. 错误使用GROUP BY：此处不需要聚合操作，GROUP BY会导致数据丢失或错误合并。
3. 字段引用错误：tb_injection表中没有status字段，正确字段是status_injection。

修正后的SQL #

WITH temp_ranked AS (
    SELECT 
        knackid,
        post_date2,
        ROW_NUMBER() OVER (PARTITION BY knackid ORDER BY post_date2 DESC) AS rn
    FROM tb_injectiontemp
    WHERE knackid = 331293
),
injection_ranked AS (
    SELECT 
        tanggal,
        knackid,
        status_injection,
        idinject,
        ROW_NUMBER() OVER (PARTITION BY knackid ORDER BY tanggal DESC) AS rn
    FROM tb_injection
    WHERE knackid = 331293
)
SELECT 
    t.knackid,
    t.post_date2,
    i.idinject,
    i.tanggal,
    i.status_injection,
    n.created_at
FROM temp_ranked t
JOIN injection_ranked i ON t.knackid = i.knackid AND t.rn = i.rn
LEFT JOIN tb_notify_esim n ON i.idinject = n.idinject
ORDER BY i.tanggal DESC;

修正逻辑说明 #

1. 给记录加行号：分别对tb_injectiontemp和tb_injection按knackid分组，按时间倒序生成行号，确保同组内的记录按时间顺序一一对应。
2. 基于行号关联：通过knackid+行号rn关联两张表，避免笛卡尔积，实现记录的精准匹配。
3. 左连接通知表：保留所有匹配的注入记录，关联对应的通知时间，无匹配时显示NULL。
4. 排序输出：按tanggal倒序排列，与期望结果一致。

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