能否在单CTE多语句中对输出做数学运算？实际查询报错求助

首先明确：完全可以在单个CTE中对多语句的输出执行数学运算，你的示例写法是正确的，但实际查询存在两处关键语法/逻辑错误：

错误点分析 #

1. CTE内部语法错误
   你试图将三个独立子查询直接用逗号分隔并别名成A、B、C，这不符合SQL语法规范。SQL要求CTE的定义必须是一个合法的SELECT语句（或其他DML语句），不能直接拼接多个子查询作为列。

2. 第二个子查询返回多行结果
   第二个统计逻辑（Current_Month SPOS count）中使用了group by npci，这会返回多行数据（每个npci对应一行），而不是单个数值，后续无法作为单个列值参与(A+B)-C的运算。

修正后的两种写法 #

写法一：将三个统计值作为同一SELECT的列 #

;with CTE as
(
    select
        -- 截至上月的SPOS数量
        (select count(*) from #final_merchant where cast(Onboarding_Date as Date)<='2022-09-30' and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')) as A,
        -- 当前月的SPOS数量（若需统计不同npci的数量，用count(distinct npci)；若统计总行数则去掉group by）
        (select count(distinct npci) from #npciactive
         where  cast(Onboarding_Date as Date)>='2022-10-01'  and cast(Onboarding_Date as Date)<='2022-10-31'
         and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')) as B,
        -- 当前月停用的SPOS数量
        (select count(*) from Opsmerchant where cast(Deactivation_DATE as Date)>='2022-10-01' and cast(Deactivation_DATE as Date)<='2022-10-31'
         and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')) as C
)
select (A+B)-C as Final_Count from CTE

写法二：用CROSS JOIN合并单个值的子查询 #

;with CTE as
(
    select A.A, B.B, C.C
    from
        (select count(*) as A from #final_merchant where cast(Onboarding_Date as Date)<='2022-09-30' and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')) A
    cross join
        (select count(distinct npci) as B from #npciactive
         where  cast(Onboarding_Date as Date)>='2022-10-01'  and cast(Onboarding_Date as Date)<='2022-10-31'
         and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')) B
    cross join
        (select count(*) as C from Opsmerchant where cast(Deactivation_DATE as Date)>='2022-10-01' and cast(Deactivation_DATE as Date)<='2022-10-31'
         and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')) C
)
select (A+B)-C as Final_Count from CTE

说明 #

• 若第二个统计逻辑是要计算所有符合条件的行数，而非按npci分组的数量，直接去掉group by npci即可，无需使用count(distinct npci)。
• 两种写法都能生成包含A、B、C三个单列值的一行数据，从而支持后续的数学运算。

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