如何高效通过SQL从EXPENSES表生成指定层级化费用报表？

问题背景 #

现有EXPENSES表存储层级费用数据，结构与数据如下：

EXPENSES表结构与数据 #

需要生成如下格式的目标报表：

目标报表格式 #

当前手动编写的SQL需要优化，要求：

1. 无需硬编码描述文本，直接从EXPENSES.EXPLANATION列取值
2. 提升执行效率

原SQL代码：

SELECT CASE WHEN E1 = '01' THEN 'PERSONNEL EXPENSES' WHEN E1 = '02' THEN 'GOODS AND SERVICES PURCHASE EXPENSES' ELSE NULL END AS A1, CASE WHEN E1 = '01' AND E2 = '1' THEN 'OFFICERS' WHEN E1 = '02' AND E2 = '1' THEN 'SERVICE PROCUREMENT' ELSE NULL END AS A2, AMOUNT FROM EXPENSES

解决方案 #

通过自连接关联层级编码，自动提取对应EXPLANATION值，同时结合索引优化执行效率：

优化后的SQL #

SELECT
    a1.EXPLANATION AS A1,
    CASE WHEN e.E2 IS NOT NULL THEN a2.EXPLANATION ELSE NULL END AS A2,
    CASE WHEN e.E3 IS NOT NULL THEN a3.EXPLANATION ELSE NULL END AS A3,
    CASE WHEN e.E1 = '02' AND e.E2 IS NULL AND e.E3 IS NULL THEN NULL ELSE e.AMOUNT END AS AMOUNT
FROM EXPENSES e
-- 关联获取A1层级的解释（同E1、无E2/E3的行）
LEFT JOIN EXPENSES a1 ON a1.E1 = e.E1 AND a1.E2 IS NULL AND a1.E3 IS NULL
-- 关联获取A2层级的解释（同E1/E2、无E3的行，仅当当前行有E2时）
LEFT JOIN EXPENSES a2 ON a2.E1 = e.E1 AND a2.E2 = e.E2 AND a2.E3 IS NULL AND e.E2 IS NOT NULL
-- 关联获取A3层级的解释（同E1/E2/E3的行，仅当当前行有E3时）
LEFT JOIN EXPENSES a3 ON a3.E1 = e.E1 AND a3.E2 = e.E2 AND a3.E3 = e.E3 AND e.E3 IS NOT NULL
ORDER BY e.E1, COALESCE(e.E2, '0'), COALESCE(e.E3, '0');

效率优化措施 #

1. 创建复合索引：为EXPENSES表创建(E1, E2, E3)复合索引，让自连接时快速定位对应层级行，避免全表扫描：

   CREATE INDEX idx_expenses_hierarchy ON EXPENSES(E1, E2, E3);
   

2. 精准关联条件：仅在存在对应层级编码时触发关联，减少无效计算。

核心优势 #

• 完全从EXPLANATION列动态提取描述，新增费用类别无需修改SQL
• 处理了目标表中GOODS AND SERVICES PURCHASE EXPENSES顶级行AMOUNT为空的特殊场景
• 排序逻辑保证输出顺序与目标报表完全匹配

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