自表关联查询

Question

实例：C1     C2      C3       C4                 C5         c6
记录1 总金额1 10000元 来源额度ID：null 去向额度ID：1 总用途1记录2 金额1 500元 来源额度ID:1 去向额度ID:2 用途1记录3 金额2 1000元 来源额度ID:1 去向额度ID:3 用途2记录4 金额3 2000元 来源额度ID:1 去向额度ID：4 用途3记录5 总金额2 5000元 来源额度ID:null 去向额度ID：5 总用途2记录6 金额4 100元 来源额度ID:5 去向额度ID:6 用途4记录7 金额5 100元 来源额度ID:5 去向额度ID：7 用途5...要求统计总金额1，总金额2，总金额..的汇总金额 以及每条总金额分出来的分项金额的汇总金额 以及每条总金额剩下的余额，如例子中总金额1 10000元 用掉3500元，还剩6500元 总金额2 5000元 用掉200元，还剩4800元 一共总金额有15000元 用掉3700元 还剩11300元 并且只关心各项总金额的总用途，用其中的来源去向额度ID进行总金额及分项金额的关联

Answer 1

select a.c2, a.c3, b.cost, a.c3 - b.cost, c6 
from (select * from table_name where c4 is null) a, 
( select c4, count(c3) cost   from table_name group by c4) b
where a.c1 = b.c4

Answer 2

试试这个吧，要想把所有的分类都一句话写出来太复杂了，我只写了部分分类，没有完全达到按小类相减。--C1 C2 C3 C4 C5 c6
WITH t AS
  (SELECT '总金额1' AS code,
    10000        AS amount,
    0            AS res,
    1            AS dist,
    '总用途1'       AS costType
  FROM dual
  UNION ALL
  SELECT '金额1',500,1,2,'用途1' FROM dual
  UNION ALL
  SELECT '金额2', 1000, 1 ,3, '用途2' FROM dual
  UNION ALL
  SELECT '金额3', 2000, 1 ,4, '用途3' FROM dual
  UNION ALL
  SELECT '总金额2', 5000, 0, 5 ,'总用途2' FROM dual
  UNION ALL
  SELECT '金额4', 100, 5 ,6, '用途4' FROM dual
  UNION ALL
  SELECT '金额5', 100, 5 ,7, '用途5' FROM dual
  )
SELECT
  CASE
    WHEN res1=0 AND dist1=1 THEN '小计' || decode(res,0,'收入：','支出：')
    WHEN res1=1
    AND dist1=1
    THEN '总计：'
    ELSE ''
  END AS descript ,
  amount
FROM
  (SELECT grouping(res) AS res1,
    grouping(dist)      AS dist1,
    res,
    dist,
    SUM(amount) AS amount
  FROM
    (SELECT res,
      dist,
      code,
      SUM(DECODE(res,0,amount,-amount))over(partition BY dist order by res) AS amount
    FROM
      ( SELECT * FROM t START WITH res = 0 CONNECT BY res = prior dist
      )
    )
  GROUP BY rollup(res,dist)
  ) a--结果
DESCRIPT AMOUNT                 
-------- ---------------------- 
         10000                  
         5000                   
小计收入： 15000                  
         -500                   
         -1000                  
         -2000                  
小计支出： -3500                  
         -100                   
         -100                   
小计支出： -200                   
总计：   11300                   11 rows selected

Answer 3

--多加一层自联接，可以准确显示收入与支出说明
--C1 C2 C3 C4 C5 c6
WITH t AS
  (SELECT '总金额1' AS code,
    10000        AS amount,
    0            AS res,
    1            AS dist,
    '总用途1'       AS costType
  FROM dual
  UNION ALL
  SELECT '金额1',500,1,2,'用途1' FROM dual
  UNION ALL
  SELECT '金额2', 1000, 1 ,3, '用途2' FROM dual
  UNION ALL
  SELECT '金额3', 2000, 1 ,4, '用途3' FROM dual
  UNION ALL
  SELECT '总金额2', 5000, 0, 5 ,'总用途2' FROM dual
  UNION ALL
  SELECT '金额4', 100, 5 ,6, '用途4' FROM dual
  UNION ALL
  SELECT '金额5', 100, 5 ,7, '用途5' FROM dual
  )
SELECT
  CASE
    WHEN res1=0 AND dist1=1 THEN decode(b.code,null,'收入','支出（' || b.code) || '）小计:'
    WHEN res1=1
    AND dist1=1
    THEN '总计：'
    ELSE ''
  END AS descript ,
  a.amount
FROM
  (SELECT grouping(res) AS res1,
    grouping(dist)      AS dist1,
    res,
    dist,
    SUM(amount) AS amount
  FROM
    (SELECT res,
      dist,
      code,
      SUM(DECODE(res,0,amount,-amount))over(partition BY dist order by res) AS amount
    FROM
      ( SELECT * FROM t START WITH res = 0 CONNECT BY res = prior dist
      )
    )
  GROUP BY rollup(res,dist)
  ) a 
  left join t b
  on a.res = b.dist
  order by a.res,a.dist--结果
DESCRIPT       AMOUNT                 
-------------- ---------------------- 
               10000                  
               5000                   
收入）小计:     15000                  
               -500                   
               -1000                  
               -2000                  
支出（总金额1）小计: -3500                  
               -100                   
               -100                   
支出（总金额2）小计: -200                   
总计：         11300                   11 rows selected