本文介绍了如何使用Oracle(大型网站数据库平台) 中的Connect by 子句,并结合一个实例,完成了对一张资产负债表的计算。
Oracle
(大型网站数据库平台) 中Connect By 子句对在关系表上表现层次关系提供了方便。使用Connect by 子句需要在表中定义两个字段,一个是父节点字段,一个是节点字段。其中节点字段一般来说是主键。 例如我们作一张资产负债表 数据来源:
http://www.adbc.com.cn/XXLR1.ASP?ID=5211资 产 | 期末余额 | 负债及所有者权益 | 期末余额 |
流动资产 | 4256.45 | 流动负债 | 7453.74 |
现金 | 2.00 | 短期存款 | 305.54 |
存放中央银行款项 | 160.77 | 财政性存款 | 411.80 |
存放同业款项 | 18.34 | 向中央银行借款 | 6485.05 |
短期贷款 | 4103.41 | 同业存放款项 | 2.15 |
其他流动资产 | 71.93 | 其他流动负债 | 249.20 |
长期资产 | 3287.75 | 长期负债 | 0.07 |
中长期贷款 | 3262.89 | 发行长期债券 | |
减:贷款呆账准备 | 73.71 | 其他长期负债 | 0.07 |
固定资产净值 | 77.58 | | |
其他长期资产 | 20.99 | | |
无形、递延及其它资产 | 0.52 | 所有者权益 | 190.91 |
| | 其中:实收资本 | 165.15 |
资产总计 | 7644.72 | 负债及所有者权益合计 | 7644.72 |
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 bs_name,bs_value from balance_sheet connect by prior bs_id = bs_pidstart with bs_pid = 0 –可以省略流动资产 | 4256.45 |
现金 | 2 |
存放中央银行款项 | 160.77 |
存放同业款项 | 18.34 |
短期贷款 | 4103.41 |
其他流动资产 | 71.93 |
长期资产 | 3287.75 |
中长期贷款 | 3262.89 |
减:贷款呆账准备 | 73.71 |
固定资产净值 | 77.58 |
其他长期资产 | 20.99 |
无形、递延及其它资产 | 0.52 |
资产总计 | 7644.72 |
流动负债 | 7453.74 |
短期存款 | 305.54 |
财政性存款 | 411.8 |
向中央银行借款 | 6485.05 |
同业存放款项 | 2.15 |
其他流动负债 | 249.2 |
长期负债 | 0.07 |
发行长期债券 | |
其他长期负债 | 0.07 |
所有者权益 | 190.91 |
其中:实收资本 | 165.15 |
负债及所有者权益合计 | 7644.72 |
显示一个节点的数据
select bs_name,bs_value from balance_sheet connect by prior bs_id = bs_pidstart with bs_pid = 1 其中connect by 定义父子连接关系start with 定义开始节点,这个子句可以省略,表示自动将全部节点展开流动资产 | 4256.45 |
现金 | 2 |
存放中央银行款项 | 160.77 |
存放同业款项 | 18.34 |
短期贷款 | 4103.41 |
其他流动资产 | 71.93 |
(流动资产节点数据) 显示层次结构 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
流动资产 | 4256.45 |
现金 | 2 |
存放中央银行款项 | 160.77 |
存放同业款项 | 18.34 |
短期贷款 | 4103.41 |
其他流动资产 | 71.93 |
长期资产 | 3287.75 |
中长期贷款 | 3262.89 |
减:贷款呆账准备 | 73.71 |
固定资产净值 | 77.58 |
其他长期资产 | 20.99 |
无形、递延及其它资产 | 0.52 |
资产总计 | 7644.72 |
流动负债 | 7453.74 |
短期存款 | 305.54 |
财政性存款 | 411.8 |
向中央银行借款 | 6485.05 |
同业存放款项 | 2.15 |
其他流动负债 | 249.2 |
长期负债 | 0.07 |
发行长期债券 | |
其他长期负债 | 0.07 |
所有者权益 | 190.91 |
其中:实收资本 | 165.15 |
负债及所有者权益合计 | 7644.72 |
(根据层次来实现缩进风格) 以下功能 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 = 7ORDER SIBLINGS BY bs_value desc
流动资产 | 4256.45 |
短期贷款 | 4103.41 |
存放中央银行款项 | 160.77 |
其他流动资产 | 71.93 |
存放同业款项 | 18.34 |
现金 | 2 |
长期资产 | 3287.75 |
中长期贷款 | 3262.89 |
固定资产净值 | 77.58 |
减:贷款呆账准备 | 73.71 |
其他长期资产 | 20.99 |
取遍历路径 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
流动资产 | 流动资产 | 4256.45 |
流动资产|现金 | 现金 | 2 |
流动资产|存放中央银行款项 | 存放中央银行款项 | 160.77 |
流动资产|存放同业款项 | 存放同业款项 | 18.34 |
流动资产|短期贷款 | 短期贷款 | 4103.41 |
流动资产|其他流动资产 | 其他流动资产 | 71.93 |
层次计算 这里层次计算是指根据父子节点关系进行汇总,也就是说 父节点 = 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)
长期负债 | 0.07 |
长期资产 | 3287.75 |
流动负债 | 7453.74 |
流动资产 | 4356.45 |
所有者权益 | 165.15 |
| 30478.88 |
竟然有意外的收获,原表中的数据流动资产是错的!!!也许是我对业务知识了解不够。如果您知道原因,还清指点。数据的最后一行是对所有原表第一层节点的叠加,如果希望得到资产和负债的总计数据,需要对节点顺序进行重新调整,我的想法是建立一个表示汇总关系的逻辑的BS_LID 和BS_LParID 。