求助：SQL Server视图中无法复用计算列的解决方案

给你几个实用的方法来避免重复编写冗长的求和公式：

方法1：使用CTE（公共表表达式）分步计算 #

CTE可以让你按步骤递推计算，每一步都能直接用上一步的结果，逻辑清晰，列数再多也容易扩展：

WITH Step1 AS (
    SELECT 
        Amount,
        paid1, paid2, paid3, paid4, paid5, paid6,
        Remain1 = Amount - paid1
    FROM YourTableName
),
Step2 AS (
    SELECT 
        *,
        Remain2 = Remain1 - paid2
    FROM Step1
),
Step3 AS (
    SELECT 
        *,
        Remain3 = Remain2 - paid3
    FROM Step2
),
Step4 AS (
    SELECT 
        *,
        Remain4 = Remain3 - paid4
    FROM Step3
),
Step5 AS (
    SELECT 
        *,
        Remain5 = Remain4 - paid5
    FROM Step4
)
SELECT 
    *,
    Remain6 = Remain5 - paid6
FROM Step5;

如果后续要加更多paid/remain列，只需要继续添加对应的StepN CTE即可，不用重复写所有前置paid列的总和。

方法2：嵌套子查询实现递推 #

和CTE逻辑一致，只是用嵌套子查询的写法，适合习惯这种风格的场景：

SELECT 
    *,
    Remain6 = Remain5 - paid6
FROM (
    SELECT 
        *,
        Remain5 = Remain4 - paid5
    FROM (
        SELECT 
            *,
            Remain4 = Remain3 - paid4
        FROM (
            SELECT 
                *,
                Remain3 = Remain2 - paid3
            FROM (
                SELECT 
                    *,
                    Remain2 = Remain1 - paid2
                FROM (
                    SELECT 
                        Amount,
                        paid1, paid2, paid3, paid4, paid5, paid6,
                        Remain1 = Amount - paid1
                    FROM YourTableName
                ) AS T1
            ) AS T2
        ) AS T3
    ) AS T4
) AS T5;

方法3：在表中定义计算列（长期使用场景） #

如果这些剩余列是长期需要的，可以直接把它们定义为表的计算列，这样后续查询时无需重复写计算逻辑：

-- 依次添加计算列到你的表中
ALTER TABLE YourTableName
ADD Remain1 AS (Amount - paid1);

ALTER TABLE YourTableName
ADD Remain2 AS (Remain1 - paid2);

ALTER TABLE YourTableName
ADD Remain3 AS (Remain2 - paid3);

ALTER TABLE YourTableName
ADD Remain4 AS (Remain3 - paid4);

ALTER TABLE YourTableName
ADD Remain5 AS (Remain4 - paid5);

ALTER TABLE YourTableName
ADD Remain6 AS (Remain5 - paid6);

之后不管是查询还是创建视图，都可以直接使用这些预定义的RemainX列。

用CTE创建视图的示例 #

如果需要把这个逻辑封装成视图，直接基于CTE来创建即可：

CREATE VIEW vw_PaymentRemains
AS
WITH Step1 AS (
    SELECT 
        Amount,
        paid1, paid2, paid3, paid4, paid5, paid6,
        Remain1 = Amount - paid1
    FROM YourTableName
),
Step2 AS (
    SELECT 
        *,
        Remain2 = Remain1 - paid2
    FROM Step1
),
Step3 AS (
    SELECT 
        *,
        Remain3 = Remain2 - paid3
    FROM Step2
),
Step4 AS (
    SELECT 
        *,
        Remain4 = Remain3 - paid4
    FROM Step3
),
Step5 AS (
    SELECT 
        *,
        Remain5 = Remain4 - paid5
    FROM Step4
)
SELECT 
    *,
    Remain6 = Remain5 - paid6
FROM Step5;

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