SQL查询：如何筛选累计求和未结清的发票（含差额行）

To retrieve all invoices needed to cover the balanceDue (including the partial invoice that finishes settling the balance), adjust your WHERE clause to check if the cumulative total before adding the current invoice was still less than the balance due. This ensures you include both fully applied invoices and the final partial invoice.

Approach 1: Using Cumulative Sum Minus Current Invoice #

If your runningRevenue is the cumulative sum of invoice amounts ordered descending, use this condition:

WHERE runningRevenue - invoiceAmount < 180961.00

Why this works:

• For the first invoice: runningRevenue - invoiceAmount = 0 (since it's the first row), which is less than 180961.00 → included.
• For the second invoice: runningRevenue - invoiceAmount equals the cumulative total of the first invoice (which was less than 180961.00) → included.
• Any subsequent invoices will have a prior cumulative total that already exceeds 180961.00 → excluded.

Approach 2: Using LAG() Window Function #

If you prefer explicitly referencing the previous row's cumulative total (works in most modern SQL dialects like PostgreSQL, SQL Server, BigQuery):

WHERE COALESCE(LAG(runningRevenue) OVER (ORDER BY invoiceAmount DESC), 0) < 180961.00

• LAG(runningRevenue) gets the cumulative total from the previous row.
• COALESCE handles the first row (where there's no previous row) by defaulting to 0.

Example Scenario #

Given:

• balanceDue = 180961.00
• Invoice 1: Amount = 150000.00 → runningRevenue = 150000.00
• Invoice 2: Amount = 68134.52 → runningRevenue = 218134.52

Both approaches return both invoices:

• Invoice 1 covers 150000.00 of the balance, leaving 30961.00.
• Invoice 2 covers the remaining 30961.00 (partial amount), fully settling the balance.

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