分享
 
 
 

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

王朝oracle·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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

摘要:本文介绍了如何使用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_pid

start 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_pid

start 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 = 7

ORDER 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 。

结束。

yvtong@126.com wang_qiang@china.com.cn

于 19:45 2005-6-14

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有