如何避免MySQL子查询中同一结果重复出现

核心思路 #

要实现每个发票仅对应一个工单、每个工单仅对应一个发票的匹配逻辑，需先筛选出所有符合日期条件的工单-发票对，再通过窗口函数或关联子查询去重，将一对多的关联收敛为一对一。

方法一：使用窗口函数（推荐，MySQL 8.0+支持） #

利用ROW_NUMBER()窗口函数分别对发票和工单分组排序，只保留每组的第一条记录，实现双向唯一匹配：

WITH possible_matches AS (
    SELECT
        j.Job_ID,
        j.Customer_id,
        j.Completion_Date,
        i.Invoice_ID,
        i.Invoice_Date,
        -- 按发票分组，取该发票关联的最晚完成工单（可改为ASC取最早完成工单）
        ROW_NUMBER() OVER (PARTITION BY i.Invoice_ID ORDER BY j.Completion_Date DESC) AS rn_invoice,
        -- 按工单分组，取该工单对应的最早发票（保留你原有的逻辑）
        ROW_NUMBER() OVER (PARTITION BY j.Job_ID ORDER BY i.Invoice_Date ASC) AS rn_job
    FROM Jobs j
    JOIN Invoices i 
        ON j.Customer_id = i.Customer_id
        AND i.Invoice_Date > j.Completion_Date
)
SELECT Job_ID, Customer_id, Completion_Date, Invoice_ID, Invoice_Date
FROM possible_matches
WHERE rn_invoice = 1 AND rn_job = 1;

逻辑说明 #

1. possible_matches公共表表达式先找出所有同一客户下、发票日期晚于工单完成日期的关联对；
2. rn_invoice确保每个发票仅保留一个关联工单（示例选最晚完成的，可根据业务需求调整排序规则）；
3. rn_job确保每个工单仅保留最早的关联发票（和你原逻辑一致）；
4. 最后筛选同时满足两个窗口函数序号为1的记录，实现双向唯一匹配。

方法二：关联子查询（兼容MySQL 5.7及更早版本） #

如果你的MySQL版本不支持CTE和窗口函数，可通过NOT EXISTS子查询实现去重：

SELECT
    j.Job_ID,
    j.Customer_id,
    j.Completion_Date,
    i.Invoice_ID,
    i.Invoice_Date
FROM Jobs j
JOIN Invoices i 
    ON j.Customer_id = i.Customer_id
    AND i.Invoice_Date > j.Completion_Date
-- 排除当前发票关联的其他更晚完成工单（保留最晚完成的工单）
AND NOT EXISTS (
    SELECT 1
    FROM Jobs j2
    WHERE j2.Customer_id = i.Customer_id
      AND j2.Completion_Date > j.Completion_Date
      AND j2.Completion_Date < i.Invoice_Date
)
-- 排除当前工单关联的其他更早发票（保留最早的发票）
AND NOT EXISTS (
    SELECT 1
    FROM Invoices i2
    WHERE i2.Customer_id = j.Customer_id
      AND i2.Invoice_Date > j.Completion_Date
      AND i2.Invoice_Date < i.Invoice_Date
);

逻辑说明 #

1. 第一个NOT EXISTS确保当前工单是该发票关联的最晚完成工单（无其他同客户工单完成时间更晚且早于发票日期）；
2. 第二个NOT EXISTS确保当前发票是该工单对应的最早发票（无其他同客户发票日期更早且晚于工单完成时间）；
3. 两者结合实现发票与工单的唯一关联。

为什么你之前的写法无效？ #

你尝试的AND Invoices.Invoice_ID not in (SELECT Invoice_ID FROM Inv)逻辑错误：Inv是主查询的表别名，子查询中FROM Inv相当于循环引用当前表，无法实现去重效果。

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