如何在SQL Server中的选择查询中调用CTE?

Question

如何在SQL ServerSelect查询中调用CTE？我遇到了如何在选择查询中调用 CTE 的问题，以下查询未执行。

我有 5 种不同类型的存储过程，如下所示，我在一个存储过程中执行这 5 个存储过程。问题 当单独执行每 5 个存储过程时，它们工作正常。从一个"主"存储过程内部执行这 5 个存储过程时，我遇到了问题。独立存储过程的结果(如查询(作为公共存储过程的参数。

我的存储过程：

SELECT 5 AS Count,
(
;WITH CTE (Name,CountValue,Title,DisplayOrder) AS
(
SELECT
JS.Name , 
COUNT(JA.JobId) CountValue,
DB.Name AS Title,
DB.DisplayOrder 
FROM JobAction_TBL JA WITH (NOLOCK) 
INNER JOIN JobActionStatus_TBL JS WITH (NOLOCK) 
ON JS.ActionCode = JA.ActionCode 
AND JA.IsDeleted = 0 
INNER JOIN JobHeader_TBL AS JH WITH (NOLOCK) 
ON JH.Id = JA.JobId 
AND JH.IsDeleted=0
INNER JOIN UserInOrganizationMas_TBL AS UO WITH (NOLOCK) 
ON UO.OrganizationId = JH.OrganizationId
INNER JOIN DashboardItems_TBL AS DB WITH (NOLOCK) 
ON DB.SpName='JobstatusReport_SP'
--WHERE CONVERT(VARCHAR(12),CONVERT(DATETIME,JA.ActionedDate,101),101) = '06/18/2018'
GROUP BY JS.NAME,DB.Name,DB.DisplayOrder
) 
SELECT Name, CountValue,CONVERT(VARCHAR(10),CountValue * 100 /
(SELECT SUM(CountValue) 
FROM CTE)
) + '%' AS CountPer ,Title,DisplayOrder
FROM CTE
FOR XML PATH ('JobStatusList'), ROOT ('JobStatusReport'), TYPE)
FOR XML PATH ('Dashboard'), ROOT ('Dashboard'), TYPE

Answer 1

为中间结果创建另一个 CTE 并改为调用它。

;with CTE (Name, CountValue, Title, DisplayOrder) as
(
select
JS.Name
,count(JA.JobId) as CountValue
,DB.Name as Title
,DB.DisplayOrder
from
JobAction_TBL JA with (nolock)
join
JobActionStatus_TBL JS with (nolock)
on JS.ActionCode = JA.ActionCode
and JA.IsDeleted = 0
join
JobHeader_TBL as JH with (nolock)
on JH.Id = JA.JobId
and JH.IsDeleted = 0
join
UserInOrganizationMas_TBL as UO with (nolock)
on UO.OrganizationId = JH.OrganizationId
join
DashboardItems_TBL as DB with (nolock)
on DB.SpName = 'JobstatusReport_SP'
group by
JS.NAME
,DB.Name
,DB.DisplayOrder
)
,CTE2 as
(
select
Name
,CountValue
,convert(varchar(10), CountValue * 100 / (select sum(CountValue) from CTE)) + '%' as CountPer
,Title
,DisplayOrder
from
CTE
)
-- CALL THE CTE's