SQL自连接查询返回错误结果：附测试数据集与待排查语句

• 时间判断方向完全颠倒：原条件tbl1.created_at < (tbl2.created_at + INTERVAL 1 DAY)仅能筛出tbl2创建时间晚于tbl1、或仅比tbl1早不到1天的记录，完全不符合业务里“回退记录（status=2、reason='sometext'）发生在对应供应商状态3提交之后，且两者间隔不超过1天”的匹配逻辑。
• 缺失最近邻匹配约束：仅靠orderID相同就做自连接，会把所有同订单下的状态2、状态3记录无规则配对，无法定位回退节点对应的前序提交节点。
• 聚合逻辑违规：group by tbl2.supplierID后，SELECT里的tbl1.orderID、tbl1.created_at未纳入聚合规则，在严格SQL模式下会直接执行报错；就算放开模式限制，也会随机取同supplier下的任意字段值，结果完全不可控。
• 连接类型使用错误：left join后在WHERE子句中直接筛选tbl2.status=3，会让左连接退化为无意义的inner join；且未加tbl2.SupplierID IS NOT NULL的过滤条件，会混入无供应商归属的无效状态3记录。
• 输出字段匹配错误：原语句输出的是tbl1（回退记录）的创建时间，而非业务要求的状态3提交记录的创建时间，字段来源与目标输出完全不匹配。

匹配规则对齐 #

业务本质是为每一条「status=2、reason='sometext'」的回退流程节点，向前回溯匹配离它最近的、带有效非空SupplierID的前序状态3提交节点，要求两个节点的时间间隔不超过24小时，最终输出匹配到的状态3节点完整信息。

修正后可直接运行的SQL #

WITH process_nodes AS (
    SELECT
        orderID,
        SupplierID,
        Status,
        Reason,
        `Created At`
    FROM tblxyz
)
SELECT
    submit.orderID,
    submit.SupplierID,
    submit.Status,
    submit.Reason,
    submit.`Created At`
FROM process_nodes rollback
INNER JOIN process_nodes submit
    ON rollback.orderID = submit.orderID
    -- 回退记录发生在对应提交节点之后
    AND rollback.`Created At` > submit.`Created At`
    -- 两者间隔不超过1天
    AND rollback.`Created At` < DATE_ADD(submit.`Created At`, INTERVAL 1 DAY)
    -- 筛选回退标记节点
    AND rollback.Status = 2 
    AND rollback.Reason = 'sometext'
    -- 筛选带有效供应商的提交节点
    AND submit.Status = 3
    AND submit.SupplierID IS NOT NULL
-- 每个回退节点仅匹配时间上最接近的前序提交节点
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY rollback.`Created At`
    ORDER BY submit.`Created At` DESC
) = 1

结果验证 #

对测试数据集执行上述SQL，会匹配到2条符合预期的结果：

1. 回退节点（23-01-2021 12:25）向前匹配到最近的提交节点为23-01-2021 06:25、SupplierID=265的状态3记录
2. 回退节点（26-01-2021 12:43）向前匹配到最近的提交节点为26-01-2021 06:43、SupplierID=324的状态3记录

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