Получение первого экземпляра определенного типа узла в запросе древовидной структуры с использованием SQL

Question

Я разрабатываю программное обеспечение для проектирования электрооборудования, которое будет моделировать электрическую систему электропитания от входящей в сеть электропитания вплоть до отдельных цепей, таких как компьютеры и кофемашины.

Я хочу дать каждому компоненту системы отдельную таблицу. например. Трансформаторы, нагрузки, кабели, силовые панели (в этом примере называемые шинами).

Каждый компонент может быть связан с одним или многими другими компонентами. Я использую родительскую / дочернюю таблицу для управления соединениями и планирую использовать CTE для получения иерархической древовидной структуры для данного компонента.

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

Я разработал запрос, который может обработать это, как показано ниже.

Однако это работает только для выбора одного компонента в CTE. Я ищу способ выбрать все шины и их подключенное напряжение (ближайший trafo или Utility). Единственное решение, которое я могу придумать, это использовать табличную функцию в приведенном выше запросе. Есть ли лучший способ сделать это.

CREATE TABLE #componentConnection
    (componentConnectionID int, parentComponentID varchar(4), childComponentID int)
;

INSERT INTO #componentConnection
    (componentConnectionID, parentComponentID, childComponentID)
VALUES
    (1, '13', 18),
    (2, '13', 19),
    (3, '13', 20),
    (4, '13', 21),
    (5, '13', 22),
    (6, '13', 23),
    (7, '14', 24),
    (8, '14', 25),
    (9, '14', 26),
    (10, '14', 27),
    (11, '14', 28),
    (12, '14', 29),
    (13, '15', 30),
    (14, '15', 31),
    (15, '15', 32),
    (16, '15', 33),
    (17, '15', 34),
    (18, '15', 35),
    (19, '16', 36),
    (20, '16', 37),
    (21, '16', 38),
    (22, '16', 39),
    (23, '16', 40),
    (24, '16', 41),
    (25, '1', 5),
    (27, '5', 13),
    (28, NULL, 1),
    (29, '18', 6),
    (30, '6', 11),
    (31, '11', 7),
    (32, '7', 14)
;


CREATE TABLE #component
    (componentID int, componentName varchar(8), componentType varchar(7))
;

INSERT INTO #component
    (componentID, componentName, componentType)
VALUES
    (1, 'Utility1', 'utility'),
    (2, 'Utility2', 'utility'),
    (3, 'utility3', 'utility'),
    (4, 'utility4', 'utility'),
    (5, 'Cable1', 'cable'),
    (6, 'Cable2', 'cable'),
    (7, 'Cable3', 'cable'),
    (8, 'Cable4', 'cable'),
    (9, 'Cable5', 'cable'),
    (10, 'Cable6', 'cable'),
    (11, 'Trafo1', 'trafo'),
    (12, 'Trafo2', 'trafo'),
    (13, 'Bus1', 'bus'),
    (14, 'Bus2', 'bus'),
    (15, 'Bus3', 'bus'),
    (16, 'Bus4', 'bus'),
    (17, 'Bus5', 'bus'),
    (18, 'cub1', 'cir'),
    (19, 'cub2', 'cir'),
    (20, 'cub3', 'cir'),
    (21, 'cub4', 'cir'),
    (22, 'cub5', 'cir'),
    (23, 'cub6', 'cir'),
    (24, 'cub1', 'cir'),
    (25, 'cub2', 'cir'),
    (26, 'cub3', 'cir'),
    (27, 'cub4', 'cir'),
    (28, 'cub5', 'cir'),
    (29, 'cub6', 'cir'),
    (30, 'cub1', 'cir'),
    (31, 'cub2', 'cir'),
    (32, 'cub3', 'cir'),
    (33, 'cub4', 'cir'),
    (34, 'cub5', 'cir'),
    (35, 'cub6', 'cir'),
    (36, 'cub1', 'cir'),
    (37, 'cub2', 'cir'),
    (38, 'cub3', 'cir'),
    (39, 'cub4', 'cir'),
    (40, 'cub5', 'cir'),
    (41, 'cub6', 'cir')
;

CREATE TABLE #utility
    ([utilityID] int, [componentID] int, [utlityKV] float)
;

INSERT INTO #utility
    ([utilityID], [componentID], [utlityKV])
VALUES
    (1, 1, 0.4),
    (2, 2, 0.208),
    (4, 3, 0.48),
    (5, 4, 0.208)
;

CREATE TABLE #transformer
    ([transformerID] int, [componentID] int, [facilityID] int, [transformerName] varchar(4), [transformerPrimaryTapKv] float, [transformerSecondaryTapKv] float, [transformerPrimaryKv] float, [transformerSecondaryKv] float)
;

INSERT INTO #transformer
    ([transformerID], [componentID], [facilityID], [transformerName], [transformerPrimaryTapKv], [transformerSecondaryTapKv], [transformerPrimaryKv], [transformerSecondaryKv])
VALUES
    (3, 11, 1, NULL, 0.48, 0.208, 0.48, 0.208),
    (4, 12, 2, NULL, 0.48, 0.4, 0.48, 0.4)
;

CREATE TABLE #Bus
    ([busID] int, [busTypeID] int, [componentID] int, [bayID] int, [busName] varchar(4), [busConductorType] varchar(6), [busRatedCurrent] int)
;

INSERT INTO #Bus
    ([busID], [busTypeID], [componentID], [bayID], [busName], [busConductorType], [busRatedCurrent])
VALUES
    (8, 1, 13, 1, 'bus1', 'Copper', 60),
    (9, 1, 14, 1, 'bus2', 'copper', 50),
    (10, 2, 15, 1, 'bus3', 'copper', 35),
    (11, 2, 16, 1, 'bus4', 'copper', 35),
    (13, 1, 17, 1, 'bus5', 'copper', 50)

;

WITH CTE AS (SELECT        childComponentID AS SourceID, childComponentID, 0 AS depth
                                                  FROM            #ComponentConnection
                                                  UNION ALL
                                                  SELECT        C1.SourceID, C.childComponentID, c1.depth + 1 AS depth
                                                  FROM            #ComponentConnection AS C INNER JOIN
                                                                           CTE AS C1 ON C.parentComponentID = C1.childComponentID)
    SELECT  childComponentID,b.busName, min(depth)
    --,c.componentType
    ,isnull(t.transformerSecondaryKv,u.utlityKV) kV
     FROM            CTE AS CTE1 
     join #Component c
     on CTE1.SourceID = c.componentID 
     left join #Utility u
     on CTE1.SourceID = u.componentID 
     left join #Transformer t
     on CTE1.SourceID = t.componentID 
     LEFT JOIN #Bus b
     on cte1.childComponentID = b.componentID 
     where busName is not null and c.componentType in ('Utility','trafo')
     group by childComponentID,b.busName,isnull(t.transformerSecondaryKv,u.utlityKV)
     order by depth

Желаемый результат будет следующим для автобуса. Я хочу перечислить все автобусы и связанные с ними напряжения. Я бы выбрал все из таблицы Bus и вывел напряжение из иерархической структуры

Результат

BusName | Voltage
Bus 1   |  0.4
Bus 2   |  0.208
Bus 3   |  etc