通过使用包含多个语句的单个CTE.我可以对CTE输出执行数学运算以得到最终输出吗?

Question

下面是我的CTE示例，这只是一个示例，我的实际查询是不同的

;with CTE as 
( select 1+1 as A,2+2 as B,3+3 as C
)
select (A+B)-C as Final_Count from CTE

在上面的例子A,B &C代表计数，我从三个不同的表中得到但是当我运行查询时它显示了错误n当我运行这个例子时它显示了输出

谁来帮我一下?我哪里出错了?

这是我的实际查询。

;with CTE as
(
--Till prev mth SPOS count
(select count(*) from #final_merchant where cast(Onboarding_Date as Date)<='2022-09-30' and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')
--66149
) A,
(
--Current_Month SPOS count
select count(*) 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')
group by npci
--2201
) B,
(
--Deactivated_SPOS_In_Current_Month
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')  
--13
) C)
select (A+B)-C Final_Count from CTE

Answer 1

CTE需要是一个SELECT查询，所以您还必须为所有子查询添加一个SELECT

;with CTE as
(SELECT 
--Till prev mth SPOS count
(select count(*) from #final_merchant where cast(Onboarding_Date as Date)<='2022-09-30' and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')
--66149
) As A, 
(
--Current_Month SPOS count
select count(*)  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')
group by npci
--2201
) AS B, 
(
--Deactivated_SPOS_In_Current_Month
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')  
--13
) C)
select (A+B)-C AS Final_Count from CTE;