时间戳

首页 > 解决方案 > 在 SQL Server 中将 CTE 转换为临时表以获取所有可能的父级

sql - 在 SQL Server 中将 CTE 转换为临时表以获取所有可能的父级

问题描述

我有一个用户表，我在其中维护父子关系，并且我想生成结果，其中所有用户 ID 及其父 ID 和所有可能的分层父级作为逗号分隔的字符串。

我的表结构如下。

CREATE TABLE [hybarmoney].[Users](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,   
    [USERID] [nvarchar](100) NULL,
    [REFERENCEID] [bigint] NULL 
)

我正在使用下面的 CTE 得到结果

;WITH Hierarchy (
    ChildId
    ,ChildName
    ,ParentId
    ,Parents
    )
AS (
    SELECT Id
        ,USERID
        ,REFERENCEID
        ,CAST('' AS VARCHAR(MAX))
    FROM hybarmoney.Users AS FirtGeneration
    WHERE REFERENCEID = 0

    UNION ALL

    SELECT NextGeneration.ID
        ,NextGeneration.UserID
        ,Parent.ChildId
        ,CAST(CASE 
                WHEN Parent.Parents = ''
                    THEN (CAST(NextGeneration.REFERENCEID AS VARCHAR(MAX)))
                ELSE (Parent.Parents + ',' + CAST(NextGeneration.REFERENCEID AS VARCHAR(MAX)))
                END AS VARCHAR(MAX))
    FROM hybarmoney.Users AS NextGeneration
    INNER JOIN Hierarchy AS Parent ON NextGeneration.REFERENCEID = Parent.ChildId
    )
SELECT *
FROM Hierarchy
ORDER BY ChildId
OPTION (MAXRECURSION 0)

从上述 CTE 获得的结果

但是我有 MAXRECURSION 的限制，当我用谷歌搜索时，我知道临时表是一种替代解决方案，但我无法做到这一点，而且我不想让所有可能的顶级父母，为了我的目的，我想为每个用户找到 15 个层次的父级。如果可能的话，是否可以出于我的目的使用临时表。

标签： sqlsql-servertsqlcommon-table-expressiontemp-tables

解决方案

为了只获得NCTE 级别，您可以做的是创建一个额外的列，您可以在其中跟踪每个级别。

;WITH Hierarchy (
    ChildId
    ,ChildName
    ,ParentId
    ,LEVEL
    ,Parents
    )
AS (
    SELECT Id
        ,USERID
        ,REFERENCEID
        ,0 AS LEVEL
        ,CAST('' AS VARCHAR(MAX))
    FROM hybarmoney.Users AS FirtGeneration
    WHERE REFERENCEID = 0

    UNION ALL

    SELECT NextGeneration.ID
        ,NextGeneration.UserID
        ,Parent.ChildId
        ,LEVEL+1 AS LEVEL
        ,CAST(CASE 
                WHEN Parent.Parents = ''
                    THEN (CAST(NextGeneration.REFERENCEID AS VARCHAR(MAX)))
                ELSE (Parent.Parents + ',' + CAST(NextGeneration.REFERENCEID AS VARCHAR(MAX)))
                END AS VARCHAR(MAX))
    FROM hybarmoney.Users AS NextGeneration
    INNER JOIN Hierarchy AS Parent ON NextGeneration.REFERENCEID = Parent.ChildId
    )
SELECT *
FROM Hierarchy
WHERE LEVEL <= 15
ORDER BY ChildId
OPTION (MAXRECURSION 0)

假设我正确理解了您的以下陈述，这是可行的：“出于我的目的，我想为每个用户找到 15 级分层父级”，您实际上是指单个用户的 15 级分层父级（在您的情况下REFERENCEID=0）。

如果您希望它为表中的每个用户生成一个包含 15 个分层父级的hybarmoney.Users列表，则将您的 CTE 移动到表值函数中并实施类似的解决方案，如此处所述。

推荐阅读