基于Accounts表构建账户层级结构的SQL实现需求

现有表accounts的结构如下：

CREATE TABLE accounts (
le_parent varchar2(100),
legal_entity varchar2(100),
trans_account_parent varchar2(100) ,
trans_account varchar2(100),
accounted_net number,
period varchar2(10)
);

插入数据的SQL语句（注：原语句中的DEPARTMENT应为accounts）：

INSERT INTO accounts VALUES ('yayasan pahang', 'yayasan pahang', 'cash transfer','908B1-palang',300,'Jul-22');
INSERT INTO accounts VALUES ('yayasan pahang', 'yayasan pahang', 'cash transfer','A2462-palang',-300,'Jun-23');
INSERT INTO accounts VALUES ('yayasan pahang', 'yayasan pahang', 'payment','a5365-loindm',500,'Jul-22');

需求：修改SQL语句，将查询结果转换为层级格式：

• 新增account hierarchy列作为层级节点，依次包含le_parent、legal_entity、trans_account_parent、trans_account四个层级
• 仅在trans_account节点的行显示对应period的accounted_net值
• 层级节点无重复
• 仅使用简单SQL，不能使用PL/SQL

可以通过UNION ALL拼接四个层级的记录，配合DISTINCT去重，再通过排序字段保证层级的正确顺序，SQL语句如下：

SELECT DISTINCT
    account_hierarchy,
    CASE WHEN level_num = 4 THEN accounted_net END AS accounted_net,
    CASE WHEN level_num = 4 THEN period END AS period
FROM (
    -- 第一层：le_parent
    SELECT
        le_parent AS account_hierarchy,
        1 AS level_num,
        NULL AS accounted_net,
        NULL AS period
    FROM accounts
    UNION ALL
    -- 第二层：legal_entity
    SELECT
        legal_entity AS account_hierarchy,
        2 AS level_num,
        NULL AS accounted_net,
        NULL AS period
    FROM accounts
    UNION ALL
    -- 第三层：trans_account_parent
    SELECT
        trans_account_parent AS account_hierarchy,
        3 AS level_num,
        NULL AS accounted_net,
        NULL AS period
    FROM accounts
    UNION ALL
    -- 第四层：trans_account，显示对应数值和周期
    SELECT
        trans_account AS account_hierarchy,
        4 AS level_num,
        accounted_net,
        period
    FROM accounts
)
ORDER BY level_num, account_hierarchy;

语句说明 #

1. 层级拼接：通过四次SELECT分别取出四个层级的节点，用UNION ALL合并所有记录
2. 去重处理：外层用DISTINCT去掉重复的层级节点
3. 数值显示控制：用CASE语句仅在第四层（trans_account节点）显示accounted_net和period，其余层级显示NULL
4. 层级排序：通过level_num字段保证层级从上到下的顺序，同时对同一层级的节点按名称排序

若需要保留层级的独立性（即使节点值相同也要显示对应层级的行），可去掉外层的DISTINCT，但会保留重复的节点值，可根据实际需求调整。

内容的提问来源于stack exchange，提问作者Aisha Khan