如何在T-SQL中为无父子关系的财务账户表构建层级结构

现有一张财务账户表，仅包含accountNumber（账户编号）和accountType（账户类型）字段，无父子层级关系信息。需要基于以下业务规则构建嵌套的父子层级：

• heading类型账户作为分组节点，对profitAndLoss类型账户进行分组
• 每组末尾以totalFrom类型账户收尾
• heading和totalFrom支持多层嵌套，形成树状父子结构

已通过窗口函数计算出sumRank字段用于识别父账户的切换逻辑，但编写T-SQL递归查询实现目标输出时遇到瓶颈。Python循环可轻松完成该逻辑，现寻求对应的T-SQL解决方案。

T-SQL 现有sumRank计算代码 #

WITH RankedAccounts AS (
    SELECT 
        accountNumber,
        accountType,
        -- 根据账户类型累计计算层级标识sumRank
        SUM(CASE 
            WHEN accountType = 'heading' THEN 1 
            WHEN accountType = 'totalFrom' THEN -1 
            ELSE 0 
        END) OVER (ORDER BY accountNumber) AS sumRank
    FROM YourAccountTable
)
SELECT * FROM RankedAccounts;

Python 实现代码 #

# 模拟账户数据（含计算好的sumRank）
accounts = [
    ('A1', 'heading', 1),
    ('A2', 'profitAndLoss', 1),
    ('A3', 'totalFrom', 0),
    ('A4', 'heading', 1),
    ('A5', 'heading', 2),
    ('A6', 'profitAndLoss', 2),
    ('A7', 'totalFrom', 1),
    ('A8', 'totalFrom', 0)
]

parent_stack = []
result = []

for acc in accounts:
    acc_num, acc_type, sum_rank = acc
    # 调整栈深度：弹出层级大于等于当前sumRank的节点
    while parent_stack and parent_stack[-1]['sumRank'] >= sum_rank:
        parent_stack.pop()
    
    # 确定父节点
    parent_id = parent_stack[-1]['accountNumber'] if parent_stack else None
    result.append({
        'accountNumber': acc_num,
        'accountType': acc_type,
        'parentAccountNumber': parent_id,
        'sumRank': sum_rank
    })
    
    # 仅heading和totalFrom类型节点入栈作为父节点候选
    if acc_type in ('heading', 'totalFrom'):
        parent_stack.append({'accountNumber': acc_num, 'sumRank': sum_rank})

# 打印结果
for item in result:
    print(item)

以下提供两种可行方案，均基于递归CTE实现：

方案1：栈模拟实现（贴合Python逻辑） #

通过字符串模拟栈结构，完全复刻Python的栈操作逻辑：

WITH RankedAccounts AS (
    -- 计算sumRank并添加行号，保证处理顺序
    SELECT 
        accountNumber,
        accountType,
        SUM(CASE 
            WHEN accountType = 'heading' THEN 1 
            WHEN accountType = 'totalFrom' THEN -1 
            ELSE 0 
        END) OVER (ORDER BY accountNumber) AS sumRank,
        ROW_NUMBER() OVER (ORDER BY accountNumber) AS rn
    FROM YourAccountTable
),
RecursiveHierarchy AS (
    -- 锚点成员：初始化第一行数据和栈
    SELECT 
        rn,
        accountNumber,
        accountType,
        sumRank,
        CAST(NULL AS VARCHAR(50)) AS parentAccountNumber,
        CAST(CONCAT(sumRank, ':', accountNumber) AS VARCHAR(MAX)) AS stack
    FROM RankedAccounts
    WHERE rn = 1

    UNION ALL

    -- 递归成员：逐行处理并维护栈
    SELECT 
        curr.rn,
        curr.accountNumber,
        curr.accountType,
        curr.sumRank,
        -- 从栈中提取当前节点的父节点（最后一个层级小于当前sumRank的节点）
        CASE 
            WHEN CHARINDEX(':', REVERSE(prev.stack)) > 0 THEN
                SUBSTRING(
                    prev.stack,
                    LEN(prev.stack) - CHARINDEX(':', REVERSE(prev.stack)) + 2,
                    CHARINDEX(':', REVERSE(prev.stack)) - 1
                )
            ELSE NULL
        END AS parentAccountNumber,
        -- 更新栈：先弹出层级>=当前sumRank的节点，再加入当前节点（如果是heading/totalFrom）
        CAST(
            CASE
                WHEN curr.sumRank <= (SELECT TOP 1 CAST(LEFT(value, CHARINDEX(':', value)-1) AS INT) 
                                     FROM STRING_SPLIT(prev.stack, ',') 
                                     ORDER BY CHARINDEX(':', value) DESC) THEN
                    STUFF(
                        prev.stack,
                        CHARINDEX(',' + CAST((SELECT TOP 1 CAST(LEFT(value, CHARINDEX(':', value)-1) AS INT) 
                                            FROM STRING_SPLIT(prev.stack, ',') 
                                            WHERE CAST(LEFT(value, CHARINDEX(':', value)-1) AS INT) >= curr.sumRank 
                                            ORDER BY CHARINDEX(':', value) ASC), ':') + 1,
                        LEN(prev.stack),
                        ''
                    )
                ELSE prev.stack
            END + 
            CASE WHEN curr.accountType IN ('heading', 'totalFrom') THEN CONCAT(',', curr.sumRank, ':', curr.accountNumber) ELSE '' END
            AS VARCHAR(MAX)
        ) AS stack
    FROM RecursiveHierarchy prev
    JOIN RankedAccounts curr ON curr.rn = prev.rn + 1
)
-- 输出最终结果，过滤栈字段
SELECT 
    accountNumber,
    accountType,
    parentAccountNumber,
    sumRank
FROM RecursiveHierarchy
ORDER BY rn;

方案2：窗口函数匹配父节点（更简洁） #

利用窗口函数直接查找符合条件的父节点，无需模拟栈：

WITH RankedAccounts AS (
    SELECT 
        accountNumber,
        accountType,
        SUM(CASE 
            WHEN accountType = 'heading' THEN 1 
            WHEN accountType = 'totalFrom' THEN -1 
            ELSE 0 
        END) OVER (ORDER BY accountNumber) AS sumRank,
        ROW_NUMBER() OVER (ORDER BY accountNumber) AS rn
    FROM YourAccountTable
),
ParentLookup AS (
    SELECT 
        curr.rn,
        curr.accountNumber,
        -- 找到当前节点之前，最近的sumRank = 当前sumRank-1且类型为heading/totalFrom的节点
        MAX(CASE WHEN prev.sumRank = curr.sumRank - 1 AND prev.accountType IN ('heading', 'totalFrom') THEN prev.accountNumber END) 
            OVER (ORDER BY curr.rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS parentAccountNumber,
        curr.sumRank,
        curr.accountType
    FROM RankedAccounts curr
    LEFT JOIN RankedAccounts prev ON prev.rn < curr.rn
)
SELECT 
    accountNumber,
    accountType,
    parentAccountNumber,
    sumRank
FROM ParentLookup
ORDER BY rn;

方案说明 #

• 方案1完全贴合Python的栈操作逻辑，适合复杂嵌套场景，逻辑直观但代码稍复杂
• 方案2利用窗口函数的范围聚合特性，直接匹配父节点，代码更简洁，适合层级规则清晰的场景

内容的提问来源于stack exchange，提问作者Henrik Poulsen