Oracle 中使用层次查询方便处理财务报表

Oracle  中Connect By 子句对在关系表上表现层次关系提供了方便。使用Connect by 子句需要在表中定义两个字段，一个是父节点字段，一个是节点字段。其中节点字段一般来说是主键。

 

例如我们作一张资产负债表

 

数据来源：http://www.adbc.com.cn/XXLR1.ASP?ID=5211

Create table balance_sheet (BS_ID INTEGER ,BS_PID INTEGER ,BS_NAME VARCHAR2(100) ,BS_VALUE NUMBER(10) );BS_ID  项目代码  BS_PID 项目父代码 BS_Name 项目名称 BS_VALUE 数据列

插入测试数据

  insert into balance_sheet values(1,0,"流动资产",4256.45);

  insert into balance_sheet values(2,1,"现金",2.00);

  insert into balance_sheet values(3,1,"存放中央银行款项",160.77);

  insert into balance_sheet values(4,1,"存放同业款项",18.34);

  insert into balance_sheet values(5,1,"短期贷款", 4103.41);

  insert into balance_sheet values(6,1,"其他流动资产",71.93);

  insert into balance_sheet values(7,0,"长期资产",3287.75);

  insert into balance_sheet values(8,7,"中长期贷款", 3262.89);

  insert into balance_sheet values(9,7,"减：贷款呆账准备",73.71);

  insert into balance_sheet values(10,7,"固定资产净值",77.58);

  insert into balance_sheet values(11,7,"其他长期资产",20.99);

  insert into balance_sheet values(12,0,"无形、递延及其它资产",0.52);

  insert into balance_sheet values(13,0,"资产总计",7644.72);

  insert into balance_sheet values(14,0,"流动负债",7453.74);

  insert into balance_sheet values(15,14,"短期存款",305.54);

  insert into balance_sheet values(16,14,"财政性存款",411.80);

  insert into balance_sheet values(17,14,"向中央银行借款",6485.05);

  insert into balance_sheet values(18,14,"同业存放款项",2.15);

  insert into balance_sheet values(19,14,"其他流动负债",249.20);

  insert into balance_sheet values(20,0,"长期负债",0.07);

  insert into balance_sheet values(21,20,"发行长期债券",null);

  insert into balance_sheet values(22,20,"其他长期负债", 0.07);

  insert into balance_sheet values(23,0,"所有者权益",190.91);

  insert into balance_sheet values(24,23,"其中：实收资本",165.15);

  insert into balance_sheet values(25,0,"负债及所有者权益合计",7644.72);

  commit;

 

显示全部数据：

 

显示一个节点的数据

 

显示层次结构

select (case when level = 1 then "        "||bs_name

              when level = 2 then  "                "||bs_name

         end ) bs_name

             ,bs_value from balance_sheet

    connect by prior bs_id = bs_pid

    start with bs_pid = 0

 

其中引用了level字段，表示层次,它是每张表默认的字段，其他默认的字段还有rownum

（根据层次来实现缩进风格）

 

以下功能 9i 及以上版本支持

 

层次内排序

       select (case when level = 1 then "        "||bs_name

              when level = 2 then  "                "||bs_name

         end ) bs_name

             ,bs_value from balance_sheet

    connect by prior bs_id = bs_pid

    start with bs_id = 1 or bs_id = 7

ORDER SIBLINGS BY bs_value desc

取遍历路径

   select

   ltrim(sys_connect_by_path( BS_Name,"|"),"|") path,

  (case when level = 1 then "        "||bs_name

              when level = 2 then  "                "||bs_name

     end ) bs_name

          ,bs_value

  from balance_sheet

  connect by prior bs_id = bs_pid

  start with bs_pid = 0

 

层次计算

 

这里层次计算是指根据父子节点关系进行汇总，也就是说  父节点 = SUM（子节点）。但是在财务报表父指标，不一定是子指标的叠加，也可能是几个子指标减去另外几个子指标。例如：

 

    长期资产 = 中长期贷款 贷款呆账准备 +固定资产净值 +其他长期资产。

 

为了实现这种情况，我们建一个字段BS_Dir来表示加减方向 1表示 加，-1表示减

 

这样 父节点 = SUM（子节点 * Direction）

SELECT

  SUBSTR (PATH, 0, INSTR (PATH, "|", -1, 1) - 1) Par_path ,

  sum(bs_value * bs_dir)

  FROM   (SELECT  BS_ID,BS_PID,   LTRIM (SYS_CONNECT_BY_PATH (bs_name, "|"), "|") PATH,

                   bs_value,bs_dir

              FROM balance_sheet

        CONNECT BY PRIOR bs_id = bs_pid

        START WITH bs_pid = 0)

     group by   SUBSTR (PATH, 0, INSTR (PATH, "|", -1, 1) - 1)

竟然有意外的收获，原表中的数据流动资产是错的！！！也许是我对业务知识了解不够。如果您知道原因，还清指点。数据的最后一行是对所有原表第一层节点的叠加，如果希望得到资产和负债的总计数据，需要对节点顺序进行重新调整，我的想法是建立一个表示汇总关系的逻辑的BS_LID 和BS_LParID 。