重用SQL Server公共表表达式中的结果

Question

我有一个查询，可以使用公用表表达式来检索页面的所有模块和子模块。是否可以多次使用cte的结果？

例子

WITH top_level_modules (
[AppContentModuleID]
,[SortIndex]
,[ContentHolderName]
,[OwnerType]
,[AppContentModuleGuid]
,[parent_AppContentModuleID]
,[ModuleID]
,[RenderIDTag]
,[WrapperType]
,[Level]
)
AS
( 
SELECT amcp.[AppContentModuleID]
    ,amcp.[SortIndex]
    ,amcp.[ContentHolderName]
    ,1
    ,amc.[AppContentModuleGuid]
    ,amc.[parent_AppContentModuleID]
    ,amc.[ModuleID]
    ,amc.[RenderIDTag]
    ,amc.[WrapperType]
    ,0 AS [Level]
FROM [dbo].[application_module_content_page] amcp
INNER JOIN [dbo].[application_module_content] amc on amcp.[AppContentModuleID] = amc.[AppContentModuleID]
WHERE amcp.[PageID] = @PageID
UNION
SELECT amcm.[AppContentModuleID]
    ,amcm.[SortIndex]
    ,amcm.[ContentHolderName]
    ,2
    ,amc.[AppContentModuleGuid]
    ,amc.[parent_AppContentModuleID]
    ,amc.[ModuleID]
    ,amc.[RenderIDTag]
    ,amc.[WrapperType]
    ,0
FROM [dbo].[application_module_content_masterpage] amcm
INNER JOIN [dbo].[application_module_content] amc on amcm.[AppContentModuleID] = amc.[AppContentModuleID]
WHERE amcm.[AppMasterPageID] = @MasterPageID
),
child_modules AS 
(
SELECT tlm.[AppContentModuleID]
    ,tlm.[SortIndex]
    ,tlm.[ContentHolderName]
    ,tlm.[OwnerType]
    ,tlm.[AppContentModuleGuid]
    ,tlm.[parent_AppContentModuleID]
    ,tlm.[ModuleID]
    ,tlm.[RenderIDTag]
    ,tlm.[WrapperType]
    ,tlm.[Level]
FROM top_level_modules tlm
UNION ALL
SELECT 
    amc.[AppContentModuleID]
    ,CASE WHEN amc.[SortIndex] IS NULL THEN tlm.[SortIndex] ELSE amc.[SortIndex] END
    ,null
    ,3
    ,amc.[AppContentModuleGuid]
    ,amc.[parent_AppContentModuleID]
    ,amc.[ModuleID]
    ,amc.[RenderIDTag]
    ,amc.[WrapperType]
    ,[Level] + 1 AS [Level]
FROM [dbo].[application_module_content] amc
INNER JOIN child_modules tlm on tlm.[AppContentModuleID] = amc.[parent_AppContentModuleID]
)
SELECT * 
FROM child_modules cm
ORDER BY cm.[OwnerType]
, cm.[Level]
, cm.[SortIndex]

SELECT apcs.[StyleType] 
    ,apcs.[StyleName]
    ,apcs.[StyleValue]
FROM child_modules cm
INNER JOIN dbo.[application_module_content_style] apcs 
on cm.AppContentMdouleID = apcs.AppContentMdouleID

第一个选择有效，但是第二个选择引发错误“无效的对象名称’child_modules’”。

Answer 1

从该WITH common_table_expression手册：

指定一个临时的命名结果集，称为公用表表达式（CTE）。这是从一个简单的查询派生的，并 在 单个
SELECT，INSERT，UPDATE或DELETE语句的执行范围内定义
。

So, no, you can’t extend the scope of the CTE beyond the SELECT statement it
was defined in. You will have to store the result in a temporary table or
table valued variable if you want to use the result more than once.