求报表统计函数用法

Question

各位好，目前存在如下需求：
根据查询条件得出明细数据，同时要返回按机构层级的汇总数据，用于报表分析。比如明细数据（SELECT * FROM TMP_HYIN_D）：
分行、支行、网点、开户数
——————————————————
分行A 支行A 网点A 1
分行A 支行A 网点B 3
分行A 支行B 网点C 0
分行A 支行B 网点D 2
分行A 支行C 网点E 1
分行A 支行C 网点F 5
分行A 支行C 网点G 6
分行A 支行C 网点H 8
分行A 支行D 网点I 1报表需要展现如下明细+汇总数据(
SELECT D.BRANCHNAME,D.SUBBRANCHNAME,D.CHILDBRANCHNAME,D.TRANSNUM FROM TMP_HYIN_D D
UNION 
SELECT D.BRANCHNAME,D.SUBBRANCHNAME,D.CHILDBRANCHNAME,SUM(D.TRANSNUM) TRANSNUM FROM TMP_HYIN_D D
GROUP BY ROLLUP(D.BRANCHNAME,D.SUBBRANCHNAME,D.CHILDBRANCHNAME)
)：
分行、支行、网点、开户数
——————————————————
分行A 支行A 网点A 1
分行A 支行A 网点B 3
分行A 支行A 4
分行A 支行B 网点C 0
分行A 支行B 网点D 2
分行A 支行B 2
分行A 支行C 网点E 1
分行A 支行C 网点F 5
分行A 支行C 网点G 6
分行A 支行C 网点H 8
分行A 支行C 20
分行A 支行D 网点I 1
分行A 支行D 1
分行A 27
27
由于结果集A在实际项目中已经很复杂，如果出以上混合数据需要对结果集A再做一次汇总，然后再UNION，代码很长。请问有什么方法可以简化成1句SQL? 最好可以把汇总后的结果空的地方加上"网点合计"、“支行合计”等字样。
感谢！！

Answer 1

需要使用GROUPING函数,这个找个例子模仿着就会了

Answer 2

GROUP BY GROUPING SETS((a,b,c),(a,b))

Answer 3

感谢回复！
查过资料后，确实可以实现如上功能。
SELECT D.BRANCHNAME, D.SUBBRANCHNAME, D.CHILDBRANCHNAME, SUM(D.TRANSNUM)
  FROM TMP_HYIN_D D
 GROUP BY GROUPING SETS((D.BRANCHNAME, D.SUBBRANCHNAME, D.CHILDBRANCHNAME),(D.BRANCHNAME, D.SUBBRANCHNAME), D.BRANCHNAME)
————————————————————————————
分行A 支行A 网点A 1
分行A 支行A 网点B 3
分行A 支行A 4
分行A 支行B 网点C 0
分行A 支行B 网点D 2
分行A 支行B 2
分行A 支行C 网点E 1
分行A 支行C 网点F 5
分行A 支行C 网点G 6
分行A 支行C 网点H 8
分行A 支行C 20
分行A 支行D 网点I 1
分行A 支行D 1
分行A 27不过，我还再贪心点，对于统计记录中的空项，可以手工赋值吗？“网点合计”、“支行合计”、“分行合计”，谢谢

Answer 4

把字段名改改就可以用了。我这里只有2组，你的有3组。
 
select LOAN_CAT
, (case when grouping(LN_TYP_CODE) = 1 then '按 LOAN CAT 合计' else LN_TYP_CODE end)
, COUNT(*) CNT 
, grouping(LOAN_CAT)  A
, grouping(LN_TYP_CODE) B 
from LNMLNT100
group by grouping sets(LOAN_CAT, (LOAN_CAT, LN_TYP_CODE))
;

Answer 5

select 
    (case when grouping(LOAN_CAT) = 1 and grouping(LN_TYP_CODE) = 1 then '总计' else LOAN_CAT end) as T1
, (case when grouping(LOAN_CAT) = 1 and grouping(LN_TYP_CODE) = 1 then '' 
          when grouping(LN_TYP_CODE) = 1 then LOAN_CAT || ' 小计' else LN_TYP_CODE end) as T2
, COUNT(*) as CNT 
, grouping(LOAN_CAT)  L1_IS_TOTAL
, grouping(LN_TYP_CODE) L2_IS_TOTAL
from LNMLNT100
where LOAN_CAT in ('PL','TL')
group by grouping sets((),LOAN_CAT, (LOAN_CAT, LN_TYP_CODE))
-- 或者 group by rollup(LOAN_CAT, LN_TYP_CODE)，两个 group by 得到一样的结果。
;