将 3 个外部应用合并为 1

Question

我有一个选择查询，其中我使用 3 个 OUTER APPLY 语句，一个是同一个表，第一个条件对所有条件都是相同的，其余的都不同。 我用它来获取同一字段的总和。有什么方法可以将所有这些 3 合并为 1 吗？

以下是我到目前为止所做的：

OUTER APPLY
        (
            SELECT
                PaymentAmount = SUM(PaymentAmount)
                FROM PMT
                    WHERE InvoiceId = INV.InvoiceId
                    GROUP BY InvoiceId
        )P2
        OUTER APPLY
        (
            SELECT
                PaymentAmount = SUM(PaymentAmount)
                FROM PMT
                    WHERE InvoiceId = INV.InvoiceId
                        AND PaymentBatchID <= PB.PaymentBatchID
                        GROUP BY InvoiceId
        )P3
        OUTER APPLY
        (
            SELECT
                PaymentAmount = SUM(PaymentAmount)
                FROM PMT
                    WHERE InvoiceId = INV.InvoiceId
                        AND PaymentBatchID <= PB.PaymentBatchID
                        AND PaymentID <= Pay.PaymentID
                        GROUP BY InvoiceId
        )P4

表 PMT 包含以下列

InvoiceId - INT
PaymentId - INT
PaymentBatchId - INT
PaymentAmount - DECIMAL(10,2)

我尝试在相同的外部应用程序中使用大小写，但出现以下错误

Msg 8124，级别 16，状态 1，第 134 行

指定了多个列在包含外部引用的聚合表达式中。

如果要聚合的表达式包含外部引用，则外部引用必须是表达式中引用的唯一列。

Answer 1

用例...何时为条件

OUTER APPLY
(
        SELECT
               P2_PaymentAmount = SUM(x.PaymentAmount),
               P3_PaymentAmount = SUM(CASE WHEN x.PaymentBatchID <= PB.PaymentBatchID THEN x.PaymentAmount END),
               P4_PaymentAmount = SUM(CASE WHEN x.PaymentBatchID <= PB.PaymentBatchID
                                            AND x.PaymentID <= Pay.PaymentID THEN x.PaymentAmount END)
        FROM   
               PMT x
        WHERE  
               x.InvoiceId = INV.InvoiceId
) P234