Блог

У меня есть таблица агентов и таблица иерархии.

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

Короче говоря, рекурсивно вся иерархия с уровнями должна быть извлечена с помощью подхода снизу вверх. Пожалуйста, помогите мне…

Я нашел ответ:

 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. Добавлен идентификатор агента CTE во втором выборе после ОБЪЕДИНЕНИЯ.
3. Добавлено предложение WHERE в CTE для удаления ненужных записей для самого нижнего уровня с нулевым NextAgentID .

Дайте мне знать, если у кого-нибудь есть вопросы.