提问人：Atul Kadam 提问时间：11/17/2022 最后编辑：nbkAtul Kadam 更新时间：11/17/2022 访问量：38
通过使用包含多个语句的单个 CTE。我可以对 CTE 输出执行数学运算以获得最终输出吗？
By using single CTE with multiple statements in it. can I perform mathematical operations on CTE output to get final output?
问：
下面是我的 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 代表我从 3 个不同的表中得到的计数 但是当我运行查询时，它显示错误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
答：
0赞
nbk
11/17/2022
#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;
评论
0赞
Atul Kadam
11/17/2022
谢谢@nbk它对我有用。我忘了写那个简单的词。
评论