如果将CTE 用在属于批处理的一部分的语句中

declare @s nvarchar(3) 
set @s = 'C%' 
;  -- 必须加分号
with 
t_tree as 
( 
    select CountryRegionCode from person.CountryRegion where Name like @s 
) 
select * from person.StateProvince where CountryRegionCode in (select * from t_tree) 

declare @a int
begin
set @a=(select COUNT(*) from PFT_PWK_WORKS)
end
; -- 必须加分号
with query as
(select ROW_NUMBER() over(order by pwk_start_dt desc) AS ROWNUM, * FROM PFT_PWK_WORKS where pwk_user like 'colin%')

select @a,* from query where ROWNUM BETWEEN 1 AND 40

即可分开写

with query as
(select ROW_NUMBER() over(order by pwk_start_dt desc) AS ROWNUM, * FROM PFT_PWK_WORKS where pwk_user like 'colin%')

select * from query where ROWNUM BETWEEN 1 AND 40

 

declare @a int
begin
set @a=(select SUM(ERD_AMOUNT) ERD_AMOUNT from PFT_ERD_DET where INCIDENT=35)
end
select top 1 ERD.ERD_StartHour,@a ERD_AMOUNT,er.ER_GL_COLD,ER.ER_CC_CODE,
ER.ER_CA_CODE,ER.ER_CREDITCC,ER.ER_Payee,ERD.INCIDENT from PFT_ERD_DET ERD join PFT_ER_MSTR ER on ER.FORMID=ERD.FORMID
where ERD.INCIDENT=35