树形明细报表分类小计（根据科目类型），合计问题，急！！

Question

类似树状图 用parentId区分啊

Answer 1

根据科目编码长度判断一级还是二级？
sum(decode(length(科目编码),4,null,金额))

Answer 2

我的思路是在判断类型时，过滤掉一级
DECODE (
            GROUPING (SUBJECTS.SUBJECTTYPE) + GROUPING (SUBJECTS.SUBJECTCODE),
            1, (case when 
                    (
                    SUBJECTS.SUBJECTTYPE='14010001' 
                    and 
                    (select count(subject_id) from CUSTOM_SUBJECTINFO  where pid=SUBJECTS.ID and custom_id=SUBJECTS.CUSTOM_ID)=0
                    ) 
                    then 
                    '资产小计' 
                    else '其他' end),
            2, '合计',
            SUBJECTS.SUBJECTCODE)
            SUBJECTCODE,(select count(subject_id) from CUSTOM_SUBJECTINFO  where pid=SUBJECTS.ID and custom_id=SUBJECTS.CUSTOM_ID)=0这部分用来过滤掉一级科目不过这种方式不起作用，注：上面红色部分确实可以过滤，已测试

Answer 3

select subject.id,DECODE(GROUPING(SUBJECTS.SUBJECTTYPE),
              1,
              DECODE(SUBJECTS.SUBJECTTYPE,
                     '14010001',
                     '资产小计',
                     '14010002',
                     '负债小计',
                     '14010003',
                     '损益小计',
                     '14010004',
                     '其他小计'),
              2,
              '合计',
              SUBJECTS.SUBJECTCODE) SUBJECTCODE
      from (明细结果) subjects
      GROUP BY ROLLUP(SUBJECTS.SUBJECTTYPE,
                 (SUBJECTS.SUBJECTNAME, SUBJECTS.ID))
 ORDER BY SUBJECTS.SUBJECTTYPE, SUBJECTS.SUBJECTCODE   

Answer 4

select subject.id,
DECODE(GROUPING (SUBJECTS.SUBJECTTYPE) + GROUPING (SUBJECTS.SUBJECTCODE),
0,
SUBJECTS.SUBJECTCODE,
sum(decode(
    (select count(subject_id) from CUSTOM_SUBJECTINFO  where pid=SUBJECTS.ID and custom_id=SUBJECTS.CUSTOM_ID)
    ,0,金额))
) SUBJECTCODE
      from (明细结果) subjects
      GROUP BY ROLLUP(SUBJECTS.SUBJECTTYPE,
                 (SUBJECTS.SUBJECTNAME, SUBJECTS.ID))
 ORDER BY SUBJECTS.SUBJECTTYPE, SUBJECTS.SUBJECTCODE   

Answer 5

有点错误，稍微调整下，和你上面的基本是同理的
rollup的记录用sum(decode)求和，其他记录直接返回金额
DECODE(GROUPING (SUBJECTS.SUBJECTTYPE) + GROUPING (SUBJECTS.SUBJECTCODE),
0,
金额,
sum(decode(
    (select count(subject_id) from CUSTOM_SUBJECTINFO  where pid=SUBJECTS.ID and custom_id=SUBJECTS.CUSTOM_ID)
    ,0,金额))
) 金额

Answer 6

不行就不用rollup了，直接汇总SELECT DECODE(L,0,G1||G2,1,G1||'小计',2,'合计'),C1,C2,C3,C4
(select G1,G2,C1,C2,C3,C4,0 AS L
FROM T
UNION ALL
SELECT G1,NULL AS G2,SUM(C1),SUM(C2),SUM(C3),SUM(C4),1 AS L
FROM T
WHERE NOT EXISTS (……)--写条件去掉一级科目
GROUP BY G1
UNION ALL
SELECT NULL G1,NULL AS G2,SUM(C1),SUM(C2),SUM(C3),SUM(C4),2 AS L
FROM T
WHERE NOT EXISTS (……)--写条件去掉一级科目
)ORDER BY G1,G2