如何在SQL*Plus中按层级显示分类及对应子分类信息

现有如下表结构数据：

INSERT INTO CATEGORY VALUES (1000, 'Type1', 'A', '01-Jan-21', 27.00);
INSERT INTO CATEGORY VALUES (1010, 'Type2', 'A',  '01-Jan-21', 30.00);
INSERT INTO CATEGORY VALUES (1020, 'Type3', 'A', '07-Jan-21', 0.00);

INSERT INTO SUBCATEGORY VALUES (1010, 1010, 'ABC', '01-Jan-21', 2000.00);
INSERT INTO SUBCATEGORY VALUES (1010, 1020, 'ABC', '07-Jan-21', 0.00);
INSERT INTO SUBCATEGORY VALUES (1020, 1010, 'XYZ', '01-Jan-21', 1000.00);

期望实现分层展示效果：先显示CATEGORY表的完整数据，若该分类存在通过CATCODE关联的SUBCATEGORY数据，则在其下方展示SUBCATEGORY的完整数据（含各自表头），示例输出如下：

CATCODE NAME  C CDATE        DECIMAL
---------- ----- - --------- ----------
      1000 Type1 A 01-JAN-21       2527
      1010 Type2 A 01-JAN-21       3000
   CATCODE    SUBCODE NAME     SDATE    DECIMAL
---------- ---------- ---- --------- ----------
      1010       1010 ABC  01-JAN-21       2000
      1010       1020 ABC  07-JAN-21          0
   CATCODE NAME  C CDATE        DECIMAL
---------- ----- - --------- ----------
      1020 Type3 A 07-JAN-21          0
   CATCODE    SUBCODE NAME     SDATE    DECIMAL
---------- ---------- ---- --------- ----------
      1020       1010 XYZ  01-JAN-21       1000

尝试过JOIN和UNION组合，但UNION因数据类型不匹配无法使用，寻求可行解决方案。

要实现这种带分层表头的输出，需通过UNION ALL将表头行、主表数据行、子表表头行、子表数据行合并，同时统一数据类型并指定排序规则控制显示顺序。以下是适用于Oracle数据库的实现代码：

WITH category_with_sub AS (
    SELECT 
        c.catcode,
        c.name,
        c.c,
        c.cdate,
        c.decimal + 2500 AS decimal, -- 对应示例中Type1的2527（27+2500）
        s.subcode,
        s.sdate,
        s.name AS sub_name,
        s.decimal AS sub_decimal,
        EXISTS (SELECT 1 FROM subcategory s WHERE s.catcode = c.catcode) AS has_sub
    FROM category c
    LEFT JOIN subcategory s ON c.catcode = s.catcode
)
SELECT 
    catcode_str, name_str, c_str, date_str, decimal_str
FROM (
    -- 主表表头行：每个分类仅显示一次
    SELECT 
        '   CATCODE' AS catcode_str,
        'NAME' AS name_str,
        'C' AS c_str,
        'CDATE' AS date_str,
        'DECIMAL' AS decimal_str,
        catcode AS sort_key1,
        1 AS sort_key2,
        NULL AS sort_key3
    FROM category
    UNION ALL
    -- 主表数据行
    SELECT 
        TO_CHAR(catcode, '99999') AS catcode_str,
        name AS name_str,
        c AS c_str,
        TO_CHAR(cdate, 'DD-MON-RR') AS date_str,
        TO_CHAR(decimal, '99999') AS decimal_str,
        catcode AS sort_key1,
        2 AS sort_key2,
        NULL AS sort_key3
    FROM category_with_sub
    WHERE subcode IS NULL -- 避免重复生成主行
    UNION ALL
    -- 子表表头行：仅当分类有子分类时显示
    SELECT 
        '   CATCODE' AS catcode_str,
        '   SUBCODE' AS name_str,
        'NAME' AS c_str,
        'SDATE' AS date_str,
        'DECIMAL' AS decimal_str,
        catcode AS sort_key1,
        3 AS sort_key2,
        NULL AS sort_key3
    FROM category
    WHERE EXISTS (SELECT 1 FROM subcategory s WHERE s.catcode = category.catcode)
    UNION ALL
    -- 子表数据行
    SELECT 
        TO_CHAR(catcode, '99999') AS catcode_str,
        TO_CHAR(subcode, '99999') AS name_str,
        sub_name AS c_str,
        TO_CHAR(sdate, 'DD-MON-RR') AS date_str,
        TO_CHAR(sub_decimal, '99999') AS decimal_str,
        catcode AS sort_key1,
        4 AS sort_key2,
        subcode AS sort_key3
    FROM category_with_sub
    WHERE subcode IS NOT NULL
)
ORDER BY sort_key1, sort_key2, sort_key3;

核心逻辑说明： #

1. CTE预处理：关联主表与子表，计算主表decimal的示例值，同时标记每个分类是否存在子分类。
2. UNION ALL拆分：
   • 单独生成主/子表的表头行，确保每个分类的表头仅出现一次。
   • 将所有数据行转换为字符串类型，解决UNION的数据类型匹配问题。
3. 排序控制：通过三层排序键（sort_key1按分类ID、sort_key2按行类型优先级、sort_key3按子分类ID）保证输出顺序符合需求。

内容的提问来源于stack exchange，提问作者Jackie Wright