Блог

Мне нужна помощь для запроса базы данных Oracle, где данные содержатся в разных таблицах. Я упростил его следующим образом:

 Table Catalog, fields: ID, Code, Label
Table Catalogstructure, fields: ID, Code, Label, CatalogID
Table Catalogsegment, fields: ID, Code, Label, CatalogID, ParentID, StructureID
 

Один каталог имеет одну структуру, а структура имеет много сегментов. Родительский идентификатор в разделе Catalogsegment содержит идентификатор из раздела Catalogsegment, который указан выше выбранного. Самые высокие сегменты каталогов не имеют родительского идентификатора.

Результат, который мне нужен, находится в разных строках, сначала Каталог с идентификатором, кодом и меткой, затем структура с идентификатором, кодом и меткой и, наконец, сверху вниз, все разделы каталога с идентификатором, кодом и меткой.

Примерные Данные:

 Catalog:
1000, C1, Catalog 1
2000, C2, Catalog 2

CatalogStructure:
1100, CS1, Structure 1, 1000
1200, CS2, Strcuture 2, 1000
2100, CS3, Structure 3, 2000

CatalogSegment:
1101, CSS1, Segment 1, 1000, null, 1100
1102, CSS2, Segment 2, 1000, 1101, 1100
1103, CSS3, Segment 3, 1000, 1102, 1100
1104, CSS4, Segment 4, 1000, 1003, 1100
1201, CSS5, Segment 5, 1000, null, 1200
1202, CSS6, Segment 6, 1000, 1201, 1200
2101, CSS7, Segment 7, 2000, null, 2100
2102, CSS8, Segment 8, 2000, 2101, 2100
 

Результат:

 1000, C1, Catalog 1
1100, CS1, Structure 1
1101, CSS1, Segment 1
1102, CSS2, Segment 2
1103, CSS3, Segment 3
1104, CSS4, Segment 4
1200, CS2, Structure 2
1201, CSS5, Segment 5
1202, CSS6, Segment 6
2000, C2, Catalog 2
2100, CS3, Structure 3
2101, CSS7, Segment 7
2102, CSS8, Segment 8 
 

С данными и результатами, которые вы показали, иерархия не имеет значения; вы можете просто объединить запросы к трем таблицам и упорядочить их по трем идентификаторам — там, где они присутствуют:

 with t (c_id, cs_id, css_id, code, label) as
(
  select c.id, null, null, c.code, c.label
  from catalog c
  union all
  select cs.catalogid, cs.id, null, cs.code, cs.label
  from catalogstructure cs
  union all
  select css.catalogid, css.structureid, css.id, css.code, css.label
  from catalogsegment css
)
select code, label
from t
order by c_id, cs_id nulls first, css_id nulls first;
 

Если идентификаторы сегментов не в порядке, как вы показали, и вы хотите упорядочить их в соответствии с иерархией, вы можете ввести рекурсивный CTE для подачи третьей ветви; или, проще говоря, здесь просто сделайте третью ветвь иерархической:

 with t (c_id, cs_id, css_id, id, code, label) as
(
  select c.id, null, null, c.id, c.code, c.label
  from catalog c
  union all
  select cs.catalogid, cs.id, null, cs.id, cs.code, cs.label
  from catalogstructure cs
  union all
  select css.catalogid, css.structureid, level, css.id, css.code, css.label
  from catalogsegment css
  start with parentid is null
  connect by catalogid = prior catalogid
  and structureid = prior structureid
  and parentid = prior id
)
select id, code, label
from t
order by c_id, cs_id nulls first, css_id nulls first;
 

Это не влияет на результат с вашими выборочными данными, по крайней мере, с поправкой на 1003 родителя до 1103; но если отношения родитель/потомок слегка перетасованы, это действительно меняет результат.

Попробуйте это:

 SELECT
  NVL(c.ID,NVL(s.ID,cs.ID)) as ID,
  NVL(c.CODE,NVL(s.CODE,cs.CODE)) as CODE,
  NVL(c.LABEL,NVL(s.LABEL,cs.LABEL)) as CODE
FROM
  Catalog as c,
  Catalogstructure as s,
  Catalogsegment as cs
WHERE
  c.ID = s.CATALOGID
  AND c.ID = cs.CATALOGID
ORDER BY 1
 

Вы можете использовать этот запрос, используя ROLLUP

 WITH catalog AS
(
    SELECT 1000 AS id, 'C1' AS code, 'Catalog 1' AS label FROM dual UNION ALL
    SELECT 2000, 'C2', 'Catalog 2' FROM dual 
)
, catalogstructure AS
(
    SELECT 1100 AS id, 'CS1' AS code, 'Structure 1' AS label, 1000 AS catalogid FROM dual UNION ALL
    SELECT 1200, 'CS2', 'Strcuture 2', 1000 FROM dual UNION ALL
    SELECT 2100, 'CS3', 'Structure 3', 2000 FROM dual
)
, catalogsegment AS
(
    SELECT 1101 AS id, 'CSS1' AS code, 'Segment 1' AS label, 1000 AS catalogid, null AS parentid, 1100 AS structureid FROM dual UNION ALL
    SELECT 1102, 'CSS2', 'Segment 2', 1000, 1101, 1100 FROM dual UNION ALL
    SELECT 1103, 'CSS3', 'Segment 3', 1000, 1102, 1100 FROM dual UNION ALL
    SELECT 1104, 'CSS4', 'Segment 4', 1000, 1003, 1100 FROM dual UNION ALL
    SELECT 1201, 'CSS5', 'Segment 5', 1000, null, 1200 FROM dual UNION ALL
    SELECT 1202, 'CSS6', 'Segment 6', 1000, 1201, 1200 FROM dual UNION ALL
    SELECT 2101, 'CSS7', 'Segment 7', 2000, null, 2100 FROM dual UNION ALL
    SELECT 2102, 'CSS8', 'Segment 8', 2000, 2101, 2100 FROM dual 
)
, join_table AS 
(
    SELECT c.id, c.code, c.label, 
        st.id AS st_id, st.code AS st_code, st.label AS st_label,
        se.id AS se_id, se.code AS se_code, se.label AS se_label
    FROM catalog c 
    LEFT JOIN catalogstructure st
    ON c.id = st.catalogid
    LEFT JOIN catalogsegment se
    ON c.id = se.catalogid AND st.id = se.structureid
)
, r_table AS 
(
    SELECT id, MAX(code) AS code, MAX(label) AS label,
        st_id, MAX(st_code) AS st_code, MAX(st_label) AS st_label,
        se_id, MAX(se_code) AS se_code, MAX(se_label) AS se_label
    FROM join_table
    GROUP BY ROLLUP (id, st_id, se_id)
)
SELECT 
    CASE 
        WHEN se_id IS NOT NULL THEN se_id 
        WHEN st_id IS NOT NULL THEN st_id
        ELSE id
    END AS id,
    CASE 
        WHEN se_id IS NOT NULL THEN se_code
        WHEN st_id IS NOT NULL THEN st_code
        ELSE code 
    END code,
    CASE 
        WHEN se_id IS NOT NULL THEN se_label 
        WHEN st_id IS NOT NULL THEN st_label
        ELSE label
    END AS label
    --, r.*
FROM r_table r
WHERE r.id IS NOT NULL
ORDER BY r.id NULLS FIRST, r.st_id NULLS FIRST, r.se_id NULLS FIRST; 
 

Протестировано в sqlfiddle