SQL-запрос для иерархического запроса данных в одной таблице

Question

У меня есть следующая таблица, в которой есть список предметов и объектов, связанных с теми же отношениями.

    | --------------------------------------- |
    | Предмет | Отношения | Объект |
    |---------------------------------------|
    |A          |x               |B         |
    |B          |x               |C         |
    |B          |x               |D         |
    |C          |x               |E         |
    |D          |x               |F         |
    |P          |x               |G         |
    |---------------------------------------|

Мне нужно написать SQL, чтобы получить все связанные Предметы и Объекты с заданным начальным предметом.

Например:

1. Если в качестве исходного субъекта задано "C", выходные данные должны быть "C" и "E" (потому что "C" относится к "E" с "x")
2. Если в качестве начального субъекта задано "A", выходные данные должны быть "A", "B", "C", "D", "E" и "F". Объяснение,
   • "А" относится к "Б"
   • "B" относится к "C" и "D"
   • "C" относится к "E"
   • "D" относится к "F"

Answer 1

Использовать рекурсивный запрос

with rcte as
(
  select * 
  from data
  where subject = 'a'
  union all
  select d.* 
  from data d
  join rcte r on r.object = d.subject
)
select r.subject from rcte r
union
select r.object from rcte r

демонстрация

Answer 2

Вам нужно использовать рекурсию:

DECLARE @Subject char(1) = 'A'

;WITH cte AS (  -- Your table sample
    SELECT  [Subject],
            [Relationship],
            [Object]
    FROM (VALUES
    ('A' ,'x' ,'B'),
    ('B' ,'x' ,'C'),
    ('B' ,'x' ,'D'),
    ('C' ,'x' ,'E'),
    ('D' ,'x' ,'F'),
    ('P' ,'x' ,'G')
    ) as t([Subject],[Relationship],[Object])
), rec AS ( -- Recursive CTE
    SELECT [Object]
    FROM cte 
    WHERE [Subject] = @Subject
    UNION ALL
    SELECT c.[Object]
    FROM cte c
    INNER JOIN rec r
        ON r.[Object] = c.[Subject]
)

SELECT @Subject
UNION ALL
SELECT *
FROM rec

Выход:

A
B
C
D
F
E

Answer 3

SAP HANA не поддерживает общие рекурсивные общие табличные выражения (CTE). Вместо этого для одного очень распространенного варианта использования, такого как этот (обработка иерархии), доступен определенный набор функций (с HANA 2 SP2):

with h as (SELECT * FROM HIERARCHY (
            SOURCE (select "Subject" as parent_id, 
                            "Object" as node_id 
                    from rels
                    order by "Subject" asc)
            ORPHAN ROOT ))
SELECT 
     node_id
FROM    
    HIERARCHY_DESCENDANTS (
         SOURCE h
         START WHERE parent_id ='A');

С HIERARCHY Функция HANA создает иерархическую структуру данных из ваших входных данных. Я назвал твой стол rels в этом примере. SOURCE Часть объявляет, как исходная таблица / представление должна интерпретироваться, чтобы составить иерархию. ORPHAN ROOT объявляет, что узлы, которые не связаны с другим узлом, становятся "корневыми" узлами. То есть, иерархии не обязательно должны быть правильными деревьями без циклов, но могут иметь бесхозные узлы, а также множественные корни и циклы.

Эта конструкция иерархии фиксируется в общем табличном выражении h, но также может быть помещен в представление или материализован во (временную) таблицу.

Следующим шагом является использование HIERARCHY_DESCENDANTS функция, чтобы HANA "шел" по ветви иерархии, начиная с узла "A". Это приводит к

NODE_ID
B      
C      
E      
D      
F      

При необходимости начальный узел может быть "объединен" с результирующим набором.

Более подробную информацию о функциональности иерархии можно найти в документации HANA здесь.