如何复用CTE输出以仅调用一次fn_GetUserPublications函数？

嗨，这个问题的核心在于CTE的特性——CTE是逻辑执行计划，每次引用它时SQL Server都会重新计算其内容，所以你多次引用[Source] CTE，就会导致底层的fn_GetUserPublications被反复调用。

要解决这个问题，我们只需要把[Source]的结果持久化到一个临时对象（临时表或表变量）中，这样函数只会被调用一次，后续所有操作都基于这个已计算好的结果。

优化后的完整代码（临时表版本） #

临时表适合数据量较大的场景，还能创建索引进一步提升性能：

-- 一次性获取Source数据存入临时表，仅调用一次函数
SELECT
    [UserPublication].*,
    [Publication].[ParentPublicationID] AS [ParentPublicationID]
INTO #Source
FROM
    [dbo].[fn_GetUserPublications](@userId) AS [UserPublication]
INNER JOIN [dbo].[Publication] AS [Publication] ON 
    [UserPublication].[ID] = [Publication].[ID];

-- 可选：给ID字段创建聚集索引，提升后续自连接的性能（数据量大时推荐）
CREATE CLUSTERED INDEX IX_Source_ID ON #Source(ID);

WITH [SourceWithParent] AS
(
    SELECT DISTINCT
        [SourcePublication].ID,
        [ParentPublication].[UID] AS [ParentUID]
    FROM
        #Source AS [SourcePublication]
    INNER JOIN #Source AS [ParentPublication] ON
        [ParentPublication].[ID] = [SourcePublication].[ParentPublicationID]
    -- 无需再关联Publication表：#Source已包含父ID，且ParentPublication来自函数返回的有效条目
),
[SourceWithChildren] AS
(
    SELECT
        [SourcePublication].[ID],
        CAST(
            CASE 
                WHEN EXISTS(
                    SELECT 1 FROM #Source AS [ChildPublication] 
                    WHERE [ChildPublication].[ParentPublicationID] = [SourcePublication].[ID]
                ) THEN 1 
                ELSE 0 
            END 
        AS bit) AS [HasChildren]
    FROM
        #Source AS [SourcePublication]
)

SELECT
    #Source.*,
    [SourceWithParent].[ParentUID],
    [SourceWithChildren].[HasChildren]
FROM 
    #Source
LEFT JOIN [SourceWithParent] ON
    [SourceWithParent].[ID] = #Source.[ID]
LEFT JOIN [SourceWithChildren] ON
    [SourceWithChildren].[ID] = #Source.[ID];

-- 清理临时表
DROP TABLE IF EXISTS #Source;

关键优化点说明 #

1. 函数仅调用一次：通过INTO #Source将函数结果一次性存入临时表，后续所有CTE和查询都基于这个表，彻底避免重复调用。
2. 简化关联逻辑：原SourceWithParent中多余的Publication关联被移除——因为#Source的ParentPublicationID已经来自Publication表，且ParentPublication是#Source中的有效条目（满足“父出版物在函数返回结果中”的要求）。
3. 高效计算HasChildren：用EXISTS替代COUNT，只要找到一个子条目就返回1，无需统计全部数量，逻辑更高效。
4. 可选索引优化：给ID字段创建聚集索引，能大幅提升后续自连接的查询速度（数据量大时效果明显）。

替代方案：表变量（小数据量场景） #

如果数据量较小，也可以用表变量，无需手动清理：

DECLARE @Source TABLE (
    -- 手动定义与fn_GetUserPublications返回结果一致的字段，加上ParentPublicationID
    ID INT,
    UID UNIQUEIDENTIFIER,
    Title NVARCHAR(255),
    Description NVARCHAR(MAX),
    CreatedDate DATETIME,
    ParentPublicationID INT
    -- 其他字段按需补充
);

INSERT INTO @Source
SELECT
    [UserPublication].*,
    [Publication].[ParentPublicationID] AS [ParentPublicationID]
FROM
    [dbo].[fn_GetUserPublications](@userId) AS [UserPublication]
INNER JOIN [dbo].[Publication] AS [Publication] ON 
    [UserPublication].[ID] = [Publication].[ID];

-- 后续CTE和查询逻辑与临时表版本一致，只需把#Source替换为@Source即可

现在再查看执行计划，fn_GetUserPublications只会被调用一次啦！

内容的提问来源于stack exchange，提问作者Denis Kaminsky