You need to enable JavaScript to run this app. SQL实现control_ledger、group_ledger、ledger三级层级查询及树形展示
优惠活动
大模型
产品
解决方案
定价
更多

SQL实现control_ledger、group_ledger、ledger三级层级查询及树形展示

实现方案说明

你可以根据使用场景选择以下两种方案实现需求:


方案1:SQL直接生成格式化输出(适合临时查询场景)

仅需调整现有SQL,依赖MySQL 8.0+/PostgreSQL等支持窗口函数的数据库,查询结果可以直接复制得到目标格式:

SELECT 
    CASE 
        WHEN rn_control = 1 THEN c.control_ledger_name
        WHEN rn_group = 1 THEN CONCAT('  ', b.group_ledger_name)
        ELSE CONCAT('       ', a.ledger_name)
    END AS tree_structure
FROM (
    SELECT 
        c.control_ledger_name,
        b.group_ledger_name,
        a.ledger_name,
        ROW_NUMBER() OVER (PARTITION BY c.control_ledger_name ORDER BY b.group_ledger_name, a.ledger_name) AS rn_control,
        ROW_NUMBER() OVER (PARTITION BY c.control_ledger_name, b.group_ledger_name ORDER BY a.ledger_name) AS rn_group
    FROM acc_setup_ledger as a 
    JOIN acc_setup_group_ledger as b ON a.group_ledger_id = b.id
    JOIN acc_setup_control_ledger as c ON a.control_ledger_id = c.id
    GROUP BY c.control_ledger_name, b.group_ledger_name, a.ledger_name
    ORDER BY c.control_ledger_name, b.group_ledger_name, a.ledger_name
) AS t

如果需要在不同控制台账之间加空行,可在外层查询额外加判断逻辑实现。


方案2:业务代码/脚本处理(适合需要后续逻辑操作的场景)

处理逻辑简单易维护,适配所有数据库版本,步骤如下:

  • 调整SQL保留完整三个字段的查询结果,排序规则保持ORDER BY c.control_ledger_name, b.group_ledger_name, a.ledger_name即可
  • 遍历结果集时用两个临时变量记录上一级的名称,逐行判断输出对应缩进的内容
  • 每切换一个控制台账时额外输出空行实现分隔效果

以下是Python参考实现:

# query_result为SQL查询返回的结果列表,每个元素为(control_ledger_name, group_ledger_name, ledger_name)
last_control = None
last_group = None

for row in query_result:
    control, group, ledger = row
    if control != last_control:
        if last_control is not None:
            print()
        print(control)
        last_control = control
        last_group = None
    if group != last_group:
        print(f"  {group}")
        last_group = group
    print(f"       {ledger}")

内容的提问来源于stack exchange,提问作者Julker Nien Akib

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.19 01:27:04