重用SQL Server公共表表达式中的结果 [英] Reuse results from SQL Server common table expression

问题描述

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

I have a query to retrieve all the modules and child modules for a page, using a common table expression. Is it possible to use the results from the cte more than once?

示例

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'。

The first select works, but the second select throws up the error "Invalid object name 'child_modules'."

推荐答案

来自 与common_table_expression 手册：

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

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

因此，不行，您不能将CTE的范围扩展到其定义所在的SELECT语句之外。您必须将结果存储在临时表或指定值的表中变量，如果您想多次使用结果。

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.

这篇关于重用SQL Server公共表表达式中的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！