在SQL中通过运行时分配层级实现账户层级分组

嘿，先帮你修正个建表语句的小问题：你写的外键约束逻辑不对，应该是让ParentCode关联到表的主键ID，这样才能正确表示父级账户关系。修正后的建表SQL如下：

DROP TABLE IF EXISTS COA;
CREATE TABLE COA (
 ID NUMERIC NOT NULL PRIMARY KEY,
 COACode VARCHAR(50),
 COATitle VARCHAR(50),
 ParentCode NUMERIC,
 DebitAmount NUMERIC(30),
 CONSTRAINT FK_COAParentCode FOREIGN KEY (ParentCode) REFERENCES COA(ID)
);

-- 补全你未写完的子节点示例，方便演示层级逻辑
INSERT INTO COA VALUES (1, '01', 'Expenses', NULL, NULL);
INSERT INTO COA VALUES (2, '02', 'Assets', NULL, NULL);
INSERT INTO COA VALUES (3, '03', 'Bills', NULL, NULL);
INSERT INTO COA VALUES (4, '01-01', 'Travel Expenses', 1, 1500);
INSERT INTO COA VALUES (5, '01-02', 'Meal Expenses', 1, 800);
INSERT INTO COA VALUES (6, '02-01', 'Current Assets', 2, 50000);
INSERT INTO COA VALUES (7, '02-01-01', 'Cash', 6, 10000);

接下来分不同数据库场景给你实现层级分配的方案：

一、支持递归CTE的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

这类数据库可以用WITH RECURSIVE递归查询遍历层级，给每个账户分配层级编号：

WITH RECURSIVE COAHierarchy AS (
    -- 锚点成员：根节点（无父级的账户），层级设为1
    SELECT 
        ID, 
        COACode, 
        COATitle, 
        ParentCode, 
        DebitAmount,
        1 AS Level,
        CAST(COACode AS VARCHAR(200)) AS HierarchyPath -- 可选：生成层级路径，直观展示归属
    FROM COA
    WHERE ParentCode IS NULL

    UNION ALL

    -- 递归成员：关联子节点，层级在上一级基础上加1
    SELECT 
        c.ID, 
        c.COACode, 
        c.COATitle, 
        c.ParentCode, 
        c.DebitAmount,
        ch.Level + 1 AS Level,
        CONCAT(ch.HierarchyPath, ' > ', c.COACode) AS HierarchyPath
    FROM COA c
    JOIN COAHierarchy ch ON c.ParentCode = ch.ID
)
SELECT * FROM COAHierarchy ORDER BY HierarchyPath;

输出说明：

• Level列就是每个账户的层级（根节点为1，子节点为2，孙节点为3，以此类推）
• HierarchyPath是可选字段，展示从根到当前节点的完整路径，便于快速识别层级关系

二、Oracle数据库 #

Oracle用CONNECT BY语法处理层级查询：

SELECT 
    ID,
    COACode,
    COATitle,
    ParentCode,
    DebitAmount,
    LEVEL AS Level,
    SYS_CONNECT_BY_PATH(COACode, ' > ') AS HierarchyPath -- 生成层级路径
FROM COA
START WITH ParentCode IS NULL -- 从根节点开始遍历
CONNECT BY PRIOR ID = ParentCode -- 递归关联父节点
ORDER SIBLINGS BY COACode; -- 同层级账户按编码排序

三、层级分组的常见用法 #

拿到层级后，你可以轻松实现各种业务需求：

• 按层级汇总金额：SELECT Level, SUM(DebitAmount) FROM COAHierarchy GROUP BY Level
• 筛选指定层级的账户：SELECT * FROM COAHierarchy WHERE Level = 2
• 查看某个账户的所有子节点：SELECT * FROM COAHierarchy WHERE HierarchyPath LIKE '%01%'

内容的提问来源于stack exchange，提问作者Doonie Darkoo