下面是我的 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
uj5u.com熱心網友回復：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/536865.html
標籤：数据库sql服务器sql-server-2008sql-server-2005sql-server-2012
上一篇：DAX變數作用域