自下而上方法的通用表表达式

Question

我有一个代理表和一个层次结构表。

CREATE TABLE [dbo].[Agent](
[AgentID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
CONSTRAINT [PK_Agent] PRIMARY KEY CLUSTERED 
(
    [AgentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Hierarchy](
    [HierarchyID] [int] IDENTITY(1,1) NOT NULL,
    [AgentID] [int] NULL,
    [NextAgentID] [int] NULL,
CONSTRAINT [PK_Hierarchy] PRIMARY KEY CLUSTERED 
(
    [HierarchyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--Insert to Agent

INSERT INTO [Agent]([FirstName],[LastName])VALUES('C1','C1');
INSERT INTO [Agent]([FirstName],[LastName])VALUES('C2','C2');
INSERT INTO [Agent]([FirstName],[LastName])VALUES('C3','C3');
INSERT INTO [Agent]([FirstName],[LastName])VALUES('C4','C4');

SELECT * FROM Agent;
AgentID FirstName   LastName
1       C1      C1
2       C2      C2
3       C3      C3
4       C4      C4

--Insert to Hierarchy

INSERT INTO [Hierarchy] ([AgentID],[NextAgentID]) VALUES (1,NULL);
INSERT INTO [Hierarchy] ([AgentID],[NextAgentID]) VALUES (2,1);
INSERT INTO [Hierarchy] ([AgentID],[NextAgentID]) VALUES (3,2);
INSERT INTO [Hierarchy] ([AgentID],[NextAgentID]) VALUES (2,4);
INSERT INTO [Hierarchy] ([AgentID],[NextAgentID]) VALUES (4,NULL);

SELECT * FROM Hierarchy;
HierarchyID AgentID NextAgentID
1       1   NULL
2       2   1
3       3   2
4       2   4
5       4   NULL

我使用了一个通用表表达式来确定从下到上的级别

WITH AgentHierarchy(AgentID, NextAgentID, HierarchyLevel)
AS
(
    SELECT
        H1.AgentID,
        H1.NextAgentID,
        1 HierarchyLevel
    FROM Hierarchy H1
    WHERE NOT EXISTS (SELECT 1 FROM Hierarchy H2 WHERE H2.NextAgentID = H1.AgentID)
    UNION ALL
    SELECT
        H.AgentID,
        H.NextAgentID,
        (AgentHierarchy.HierarchyLevel + 1) HierarchyLevel
    FROM Hierarchy H    
    INNER JOIN AgentHierarchy ON AgentHierarchy.NextAgentID = H.AgentID
)
SELECT DISTINCT
    AgentID,
    NextAgentID, 
    HierarchyLevel
FROM AgentHierarchy
ORDER BY AgentID, NextAgentID, HierarchyLevel;

结果是：

AgentID NextAgentID HierarchyLevel
1       NULL        3
2       1           2
3       2           1
4       NULL        1
2       4           1

我的要求是用以下方式显示这一点：

AgentID NextAgentID HierarchyLevel
1       NULL        1
2       1           1
3       2           1
3       1           2
4       NULL        1
2       4           1
3       4           2

简而言之，应递归地拉出所有具有级别的层次结构自下而上的方法。请帮我...

Answer 1

我找到了答案：

WITH AgentHierarchy(AgentID, NextAgentID, HierarchyLevel)
AS
(
    SELECT
        H1.AgentID,
        H1.NextAgentID,
        1 HierarchyLevel
    FROM Hierarchy H1
    --WHERE NOT EXISTS (SELECT 1 FROM Hierarchy H2 WHERE H2.NextAgentID = H1.AgentID)
    UNION ALL
    SELECT
        AgentHierarchy.AgentID,
        H.NextAgentID,
        (AgentHierarchy.HierarchyLevel + 1) HierarchyLevel
    FROM Hierarchy H    
    INNER JOIN AgentHierarchy ON AgentHierarchy.NextAgentID = H.AgentID
)
SELECT 
    AgentHierarchy.AgentID,
    NextAgentID, 
    HierarchyLevel
FROM AgentHierarchy
WHERE NOT (NextAgentID IS NULL AND HierarchyLevel > 1);

我做了以下更改：

1. 删除了锚查询 WHERE 子句。
2. 在 UNION 之后的第二个 select 中添加了 CTE 的 AgentID。
3. 在 CTE 中添加了 WHERE 子句以删除垃圾记录
   最底层，NextAgentID 为 NULL。

如果有人有疑问请告诉我。