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
相关产品推荐
相关产品推荐

