如何在Microsoft SQL中计算未流失与流失客户的收入差值？

你之前的两个查询是独立的结果集，无法直接做减法运算，需要把两类收入聚合到同一个查询上下文里，以下是两种可行方案：

方法一：子查询合并计算 #

将两个统计查询作为子查询嵌入主查询，实现差值计算：

SELECT 
    -- 未流失客户总收入
    (SELECT SUM([monthly charge]) 
     FROM portfolioproject..telecom_churn 
     WHERE [Customer Status] IN ('Joined', 'Stayed')) AS Non_Churned_Revenue,
    -- 流失客户总收入
    (SELECT SUM([monthly charge]) 
     FROM portfolioproject..telecom_churn 
     WHERE [Customer Status] = 'Churned') AS Churned_Revenue,
    -- 收入差值
    (SELECT SUM([monthly charge]) 
     FROM portfolioproject..telecom_churn 
     WHERE [Customer Status] IN ('Joined', 'Stayed')) - 
    (SELECT SUM([monthly charge]) 
     FROM portfolioproject..telecom_churn 
     WHERE [Customer Status] = 'Churned') AS Revenue_Difference

如果使用SQL Server，也可以用变量存储中间结果，代码更清晰：

DECLARE @NonChurned DECIMAL(18,2)
DECLARE @Churned DECIMAL(18,2)

-- 统计未流失客户收入
SELECT @NonChurned = SUM([monthly charge]) 
FROM portfolioproject..telecom_churn 
WHERE [Customer Status] IN ('Joined', 'Stayed')

-- 统计流失客户收入
SELECT @Churned = SUM([monthly charge]) 
FROM portfolioproject..telecom_churn 
WHERE [Customer Status] = 'Churned'

-- 输出结果及差值
SELECT 
    @NonChurned AS Non_Churned_Revenue,
    @Churned AS Churned_Revenue,
    @NonChurned - @Churned AS Revenue_Difference

方法二：条件聚合（推荐） #

只需要扫描一次数据表，性能更优。通过CASE语句区分客户类型，直接聚合计算两类收入及差值：

SELECT 
    SUM(CASE WHEN [Customer Status] IN ('Joined', 'Stayed') THEN [monthly charge] ELSE 0 END) AS Non_Churned_Revenue,
    SUM(CASE WHEN [Customer Status] = 'Churned' THEN [monthly charge] ELSE 0 END) AS Churned_Revenue,
    SUM(CASE WHEN [Customer Status] IN ('Joined', 'Stayed') THEN [monthly charge] ELSE 0 END) - 
    SUM(CASE WHEN [Customer Status] = 'Churned' THEN [monthly charge] ELSE 0 END) AS Revenue_Difference
FROM portfolioproject..telecom_churn

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