缺少級別的Oracle扁平化層次結構

我需要在 Oracle 12c 中展平層次結構，示例簡化輸入在左側，底部添加了圖形表示（下面的螢屏截圖）。目標輸出在右側突出顯示。

有 4 個級別，其中 Lvl_1 是層次結構的頂部，并且缺少級別。要求是缺失的級別應該用可用的最低級別的詳細資訊來填充。有多個樣本，如 P_1_2

我不知道如何解決這個問題，我已經找到了幾個 SQL Server 而不是 Oracle 的解決方案，并且沒有一個能夠管理缺少的級別標準作為這個要求

有誰做過這樣的事情？

CREATE TABLE ztest_product
(
    PRODUCT_ID VARCHAR(10)
    , PARENT_ID VARCHAR(10)
)
;

Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_0', NULL);
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_1', 'P_0');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_1_1', 'P_1');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_1_2', 'P_1');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_1_1_1', 'P_1_1');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_1_1_2', 'P_1_1');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_1_1_3', 'P_1');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_2_1_1', 'P_0');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_3', 'P_0');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_3_1_1', 'P_3');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_3_2', 'P_3');
Insert into ztest_product(PRODUCT_ID, PARENT_ID) values ('P_3_1_2', 'P_3_2');

COMMIT;

uj5u.com熱心網友回復：

您可以找到層次結構的葉節點并使用它SYS_CONNECT_BY_PATH來查找所采用的路徑，然后將其拆分為不同的級別：

SELECT lvl_1,
       COALESCE(lvl_2, lvl_1) AS lvl_2,
       COALESCE(lvl_3, lvl_2, lvl_1) AS lvl_3,
       COALESCE(lvl_4, lvl_3, lvl_2, lvl_1) AS lvl_4
FROM   (
  SELECT REGEXP_SUBSTR(
           SYS_CONNECT_BY_PATH(product_id, ','), ',([^,] )', 1, 1, NULL, 1
         ) AS lvl_1,
         REGEXP_SUBSTR(
           SYS_CONNECT_BY_PATH(product_id, ','), ',([^,] )', 1, 2, NULL, 1
         ) AS lvl_2,
         REGEXP_SUBSTR(
           SYS_CONNECT_BY_PATH(product_id, ','), ',([^,] )', 1, 3, NULL, 1
         ) AS lvl_3,
         REGEXP_SUBSTR(
           SYS_CONNECT_BY_PATH(product_id, ','), ',([^,] )', 1, 4, NULL, 1
         ) AS lvl_4
  FROM   ztest_product
  WHERE  CONNECT_BY_ISLEAF = 1
  START WITH parent_id IS NULL
  CONNECT BY PRIOR product_id = parent_id
)

其中，對于您的樣本資料，輸出：

小提琴

uj5u.com熱心網友回復：

這是一個在不使用 regexp_substr 的情況下生成缺失節點的準備查詢，如果您的資料集很大，這可能會導致性能問題：

WITH cnodes AS (
    SELECT LEVEL AS lvl, p.*, connect_by_isleaf AS is_leaf FROM ztest_product p
    START WITH product_id IN (
        SELECT product_id FROM ztest_product WHERE parent_id IS NULL
    )
    CONNECT BY PRIOR product_id = parent_id 
),
inodes AS (
    SELECT cn.* FROM cnodes cn
    WHERE NOT EXISTS(
        SELECT 1 FROM cnodes c1 WHERE cn.product_id = c1.parent_id AND cn.lvl = c1.lvl   1 AND cn.lvl < 4
    )
    AND is_leaf = 1 AND lvl < 4
)
SELECT * FROM (
    SELECT lvl, product_id, parent_id FROM (
        SELECT lvl LEVEL AS lvl, product_id, product_id AS parent_id FROM inodes
        CONNECT BY LEVEL < 5 - lvl AND PRIOR product_id = product_id AND PRIOR sys_guid() IS NOT NULL
        UNION ALL 
        SELECT lvl, product_id, parent_id FROM cnodes
    )
)
ORDER BY lvl, parent_id, product_id
;

結果，如果完整的樹缺少節點，其中添加的節點是它們自己的父節點：

1   P_0 
2   P_1 P_0
2   P_2_1_1 P_0
2   P_3 P_0
3   P_1_1   P_1
3   P_1_1_3 P_1
3   P_1_2   P_1
3   P_2_1_1 P_2_1_1
3   P_3_1_1 P_3
3   P_3_2   P_3
4   P_1_1_1 P_1_1
4   P_1_1_2 P_1_1
4   P_1_1_3 P_1_1_3
4   P_1_2   P_1_2
4   P_2_1_1 P_2_1_1
4   P_3_1_1 P_3_1_1
4   P_3_1_2 P_3_2

標籤：sql甲骨文等级制度

上一篇：Powershell添加到哈希表

下一篇：案例函式回傳Null