ORACLE树形数据

Question

--子节点和父节点的对应关系
WITH TMP AS
 (SELECT ID
        ,NAME
        ,ISMEMO
        ,CONNECT_BY_ISLEAF AS ISFLAG
        ,CONNECT_BY_ROOT   ID AS ROOT
    FROM BASIC
   WHERE CONNECT_BY_ISLEAF = '1'
  CONNECT BY PRIOR ID = PARENT_ID)
SELECT TMP.ROOT
      ,SUM(CASE
             WHEN TMP.ISMEMO = '1' AND TMP.ID <> TMP.ROOT THEN--memo，并且向父节点合计的时候为0
              0
             ELSE
              R.PLAN_VAL
           END)
  FROM BASIC_RECORD R
  JOIN TMP
    ON R.BASIC_ID = TMP.ID
 GROUP BY TMP.ROOT
 ORDER BY TMP.ROOT
试试看可以不

Answer 1

select root_nm name,
       sum(decode(t2.name,
                  root_nm,
                  plan_val,
                  decode(ismemo, '1', 0, plan_val))) plan_val
  from basic_record t3,
       (select t1.*, connect_by_root t1.name root_nm
          from basic t1
        connect by prior id = parent_id) t2
 where t2.id = t3.basic_id(+)
 group by root_nm;