这将获得完整的报告
SELECT t1.DEPARTMENT_ID
, t1.PARENT_DEP_ID
, t1.DEPARTMENT
, Sum(t2.Amount) Amount
FROM TREE_DATA t1
INNER JOIN TREE_DATA t2
ON t1.DEPARTMENT = SUBSTR(t2.DEPARTMENT, 1, LENGTH(t1.DEPARTMENT))
WHERE t1.Amount = 0
GROUP BY t1.DEPARTMENT_ID, t1.PARENT_DEP_ID, t1.DEPARTMENT
UNION ALL
SELECT DEPARTMENT_ID
, PARENT_DEP_ID
, DEPARTMENT
, Amount
FROM TREE_DATA
WHERE Amount > 0
ORDER BY DEPARTMENT
第一个查询通过修改不包含金额的分部的部门名称结构来获得滚动总和,第二个查询获得叶子。 第一个查询无法显示叶子,因为它们将被分组。我还没有找到任何列组合来获得相同的顺序,叶子似乎是无序的。