嵌套连接中按日期过滤并分组的发票自连接问题

需要对发票表（Invoices）执行自连接操作，需求是：当历史发票与当前发票的订单编号（PO_Number）相同时，将历史发票金额从当前发票中扣除。目前的自连接代码会返回对应PO下所有发票的金额总和（包含当前发票本身），但需要过滤出历史发票日期早于当前发票日期的记录，且因为查询列多、逻辑复杂，不想在连接外再做分组操作。

当前使用的代码：

SELECT Invoice_Number
       ,Invoice_Date
       ,PO_Number
       ,Amount_Invoice
       ,Amount_PastInvoices

FROM Invoices

LEFT OUTER JOIN (
                 SELECT SUM(Amount_Invoice) as Amount_PastInvoices
                        ,PO_Number
                 FROM Invoices             
                 GROUP BY PO_Number
) Past_Invoices ON Invoices.PO_Number = Past_Invoices.PO_Number

现在需要实现Past_Invoices.Invoice_Date < Invoices.Invoice_date的过滤，但嵌套连接内已经分组，不知道如何将当前发票日期传入，试过子查询但必须在连接外再次分组，寻求解决方案。

方法1：关联子查询（无需外层分组） #

直接在SELECT子句里写关联子查询，针对每一行当前发票，精准计算同PO下日期更早的发票金额总和。这种写法不用额外的连接或外层分组，逻辑直观：

SELECT 
    Invoice_Number,
    Invoice_Date,
    PO_Number,
    Amount_Invoice,
    -- 计算同PO下日期早于当前发票的历史金额总和
    (SELECT SUM(Amount_Invoice)
     FROM Invoices AS Past
     WHERE Past.PO_Number = Invoices.PO_Number
       AND Past.Invoice_Date < Invoices.Invoice_Date) AS Amount_PastInvoices,
    -- 可选：直接算出扣除后的金额
    Amount_Invoice - COALESCE((SELECT SUM(Amount_Invoice)
                               FROM Invoices AS Past
                               WHERE Past.PO_Number = Invoices.PO_Number
                                 AND Past.Invoice_Date < Invoices.Invoice_Date), 0) AS Amount_AfterDeduction
FROM Invoices;

方法2：窗口函数（高效简洁） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），用SUM() OVER()搭配条件判断是最优解——不需要自连接或子查询，直接按PO分组计算当前发票之前的金额总和：

SELECT 
    Invoice_Number,
    Invoice_Date,
    PO_Number,
    Amount_Invoice,
    -- 按PO分组，累加当前发票日期之前的所有金额
    SUM(CASE WHEN Invoice_Date < CURRENT_INV.Invoice_Date THEN Amount_Invoice ELSE 0 END) 
        OVER (PARTITION BY PO_Number) AS Amount_PastInvoices,
    -- 直接计算扣除后的金额
    Amount_Invoice - SUM(CASE WHEN Invoice_Date < CURRENT_INV.Invoice_Date THEN Amount_Invoice ELSE 0 END) 
        OVER (PARTITION BY PO_Number) AS Amount_AfterDeduction
FROM Invoices AS CURRENT_INV;

方法3：改进自连接（嵌套查询关联当前发票） #

如果一定要用自连接，可以把当前发票的PO和日期作为筛选条件传入子查询，不过这种写法本质和关联子查询类似：

SELECT 
    CURRENT_INV.Invoice_Number,
    CURRENT_INV.Invoice_Date,
    CURRENT_INV.PO_Number,
    CURRENT_INV.Amount_Invoice,
    COALESCE(Past_Invoices.Amount_PastInvoices, 0) AS Amount_PastInvoices,
    CURRENT_INV.Amount_Invoice - COALESCE(Past_Invoices.Amount_PastInvoices, 0) AS Amount_AfterDeduction
FROM Invoices AS CURRENT_INV
LEFT OUTER JOIN (
    SELECT 
        PO_Number,
        MAX_Current_Date,
        SUM(Amount_Invoice) AS Amount_PastInvoices
    FROM (
        SELECT 
            Past.PO_Number,
            CURRENT_INV.Invoice_Date AS MAX_Current_Date,
            Past.Amount_Invoice
        FROM Invoices AS Past
        JOIN Invoices AS CURRENT_INV 
            ON Past.PO_Number = CURRENT_INV.PO_Number
            AND Past.Invoice_Date < CURRENT_INV.Invoice_Date
    ) AS Filtered_Past
    GROUP BY PO_Number, MAX_Current_Date
) AS Past_Invoices 
    ON CURRENT_INV.PO_Number = Past_Invoices.PO_Number
    AND CURRENT_INV.Invoice_Date = Past_Invoices.MAX_Current_Date;

如果数据库支持窗口函数，优先选方法2，性能更好代码也更干净，完全满足你“不在连接外分组”的需求。

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