Блог

Я не уверен, что формулировка моего вопроса точна, но, короче говоря, у меня есть 4 таблицы, из которых мне нужно получить доступ к данным. ГЛУБИНА, DEPTHSET, DEPTHENTRY amp; DEPTHSETMM. Мне нужно, чтобы мои выходные данные отображали 1 строку на DEPTHSET, на ГЛУБИНУ, и я собираю данные из DEPTHENTRY с помощью функции LISTAGG. Моя проблема в том, что я не получаю строку для каждой ГЛУБИНЫ, если к ней не привязан допустимый DEPTHENTRY через таблицу сопоставления DEPTHSETMM many.

Я предоставил примеры данных с помощью предложений with, а затем мой фактический код ниже вместе с текущим результатом и моим желаемым результатом.

 WITH
DEPTH AS (
(SELECT 1 AS "DEPTHID", 'Group'  AS "NAME" FROM DUAL) UNION
(SELECT 2 AS "DEPTHID", 'Branch' AS "NAME" FROM DUAL) UNION
(SELECT 3 AS "DEPTHID", 'Area'   AS "NAME" FROM DUAL) UNION
(SELECT 4 AS "DEPTHID", 'Dept'   AS "NAME" FROM DUAL) UNION
(SELECT 5 AS "DEPTHID", 'Shift'  AS "NAME" FROM DUAL) UNION
(SELECT 6 AS "DEPTHID", 'Rpt'    AS "NAME" FROM DUAL) UNION
(SELECT 7 AS "DEPTHID", 'Code'   AS "NAME" FROM DUAL)
),
DEPTHSET AS (
(SELECT 3705 AS "DEPTHSETID", 'Idaho Set' AS "NAME" FROM DUAL)
),
DEPTHSETMM AS (
(SELECT 3705 AS "DEPTHSETID", 1410 AS "ENTRYID" FROM DUAL) UNION
(SELECT 3705 AS "DEPTHSETID", 1420 AS "ENTRYID" FROM DUAL) UNION
(SELECT 3705 AS "DEPTHSETID", 1421 AS "ENTRYID" FROM DUAL) UNION
(SELECT 3705 AS "DEPTHSETID", 1430 AS "ENTRYID" FROM DUAL) 
),
DEPTHENTRY AS (
(SELECT 1410 AS "ENTRYID", 'North West' AS "NAME", 1 AS "DEPTHID" FROM DUAL) UNION
(SELECT 1420 AS "ENTRYID", 'Zone 1' AS "NAME", 3 AS "DEPTHID" FROM DUAL) UNION
(SELECT 1421 AS "ENTRYID", 'Zone 2' AS "NAME", 3 AS "DEPTHID" FROM DUAL) UNION
(SELECT 1430 AS "ENTRYID", 'A' AS "NAME", 7 AS "DEPTHID" FROM DUAL) 
)
SELECT
DST.name AS "DEPTH_SET_NAME",
DEP.depthid AS "DEPTHID",
DEP.name AS "DEPTH_NAME",
LISTAGG(CAST(DEE.name AS varchar2(2000)), '; ') WITHIN GROUP (ORDER BY DEE.name DESC) AS "ENTRY_NAME"
FROM DEPTHSETMM DMM
  LEFT OUTER JOIN DEPTHENTRY DEE ON (DMM.entryid = DEE.entryid)
  LEFT OUTER JOIN DEPTH DEP      ON (DEE.depthid = DEP.depthid)
  LEFT OUTER JOIN DEPTHSET DST        ON (DMM.depthsetid = DST.depthsetid)
GROUP BY DST.name, DEP.depthid, DEP.name
  ORDER BY DST.name, DEP.depthid
  

Текущий вывод

 DEPTH_SET_NAME  DEPTHID  DEPTH_NAME  ENTRY_NAME
Idaho Set       1        Group       North West
Idaho Set       3        Area        Zone 2; Zone 1
Idaho Set       7        Code        A
  

Желаемый результат

 DEPTH_SET_NAME  DEPTHID  DEPTH_NAME  ENTRY_NAME
Idaho Set       1        Group       North West
Idaho Set       2        Branch      NULL
Idaho Set       3        Area        Zone 2; Zone 1
Idaho Set       4        Dept        NULL
Idaho Set       5        Shift       NULL
Idaho Set       6        Rpt         NULL
Idaho Set       7        Code        A
  

Вы можете выполнить перекрестное соединение между DEPTHSET и DEPTH , а затем внешнее соединение с остальными таблицами:

 WITH ...
SELECT
DST.name AS "DEPTH_SET_NAME",
DEP.depthid AS "DEPTHID",
DEP.name AS "DEPTH_NAME",
LISTAGG(CAST(DEE.name AS varchar2(2000)), '; ') WITHIN GROUP (ORDER BY DEE.name DESC) AS "ENTRY_NAME"
FROM DEPTHSET DST
CROSS JOIN DEPTH DEP
  LEFT OUTER JOIN DEPTHENTRY DEE ON (DEE.depthid = DEP.depthid)
  LEFT OUTER JOIN DEPTHSETMM DMM ON (DMM.entryid = DEE.entryid)
                                 AND (DMM.depthsetid = DST.depthsetid)
GROUP BY DST.name, DEP.depthid, DEP.name
  ORDER BY DST.name, DEP.depthid;

DEPTH_SET    DEPTHID DEPTH_ ENTRY_NAME                    
--------- ---------- ------ ------------------------------
Idaho Set          1 Group  North West                    
Idaho Set          2 Branch                               
Idaho Set          3 Area   Zone 2; Zone 1                
Idaho Set          4 Dept                                 
Idaho Set          5 Shift                                
Idaho Set          6 Rpt                                  
Idaho Set          7 Code   A                             
  

Обратите внимание на AND (DMM.depthsetid = DST.depthsetid) во втором внешнем соединении, заменяющем то, что раньше было косвенным условием.