Oracle中Connect By 子句对在关系表上表现层次关系提供了方便
使用Connect by 子句需要在表中定义两个字段一个是父节点字段一个是节点字段其中节点字段一般来说是主键
例如我们作一张资产负债表
数据来源
资产 期末余额 负债及所有者权益 期末余额
流动资产流动负债
现金短期存款
存放中央银行款项财政性存款
存放同业款项 向中央银行借款
短期贷款 同业存放款项
其他流动资产 其他流动负债
长期资产 长期负债
中长期贷款 发行长期债券
减贷款呆账准备 其他长期负债
固定资产净值
其他长期资产
无形递延及其它资产所有者权益
其中实收资本
资产总计 负债及所有者权益合计
Create table balance_sheet (BS_ID INTEGER BS_PID INTEGER BS_NAME VARCHAR() BS_VALUE NUMBER() );
BS_ID项目代码BS_PID 项目父代码 BS_Name 项目名称 BS_VALUE 数据列
插入测试数据
insert into balance_sheet values(流动资产);
insert into balance_sheet values(现金);
insert into balance_sheet values(存放中央银行款项);
insert into balance_sheet values(存放同业款项);
insert into balance_sheet values(短期贷款 );
insert into balance_sheet values(其他流动资产);
insert into balance_sheet values(长期资产);
insert into balance_sheet values(中长期贷款 );
insert into balance_sheet values(减贷款呆账准备);
insert into balance_sheet values(固定资产净值);
insert into balance_sheet values(其他长期资产);
insert into balance_sheet values(无形递延及其它资产);
insert into balance_sheet values(资产总计);
insert into balance_sheet values(流动负债);
insert into balance_sheet values(短期存款);
insert into balance_sheet values(财政性存款);
insert into balance_sheet values(向中央银行借款);
insert into balance_sheet values(同业存放款项);
insert into balance_sheet values(其他流动负债);
insert into balance_sheet values(长期负债);
insert into balance_sheet values(发行长期债券null);
insert into balance_sheet values(其他长期负债 );
insert into balance_sheet values(所有者权益);
insert into balance_sheet values(其中实收资本);
insert into balance_sheet values(负债及所有者权益合计);
commit;
显示全部数据
select bs_namebs_valuefrom balance_sheet
connect by prior bs_id = bs_pid
start with bs_pid = –可以省略
显示一个节点的数据
select bs_namebs_valuefrom balance_sheet
connect by prior bs_id = bs_pid
start with bs_pid =
其中connect by 定义父子连接关系
start with 定义开始节点这个子句可以省略表示自动将全部节点展开
(流动资产节点数据)
显示层次结构
select (case when level = then ||bs_name
when level = then||bs_name
end ) bs_name
bs_value from balance_sheet
connect by prior bs_id = bs_pid
start with bs_pid =
其中引用了level字段表示层次它是每张表默认的字段其他默认的字段还有rownum
（根据层次来实现缩进风格）
以下功能 i 及以上版本支持
层次内排序
select (case when level = then ||bs_name
when level = then||bs_name
end ) bs_name
bs_value from balance_sheet
connect by prior bs_id = bs_pid
start with bs_id = or bs_id =
ORDER SIBLINGS BY bs_value desc
取遍历路径
select
ltrim(sys_connect_by_path( BS_Name|)|) path
(case when level = then ||bs_name
when level = then||bs_name
end ) bs_name
bs_value
from balance_sheet
connect by prior bs_id = bs_pid
start with bs_pid =
层次计算
这里层次计算是指根据父子节点关系进行汇总也就是说父节点 = SUM（子节点）
但是在财务报表父指标不一定是子指标的叠加也可能是几个子指标减去另外几个子指标
例如
长期资产 = 中长期贷款 – 贷款呆账准备 +固定资产净值 +其他长期资产
为了实现这种情况我们建一个字段BS_Dir来表示加减方向 表示 加表示减
这样 父节点 = SUM（子节点 * Direction）
SELECT
SUBSTR (PATH INSTR (PATH | ) ) Par_path
sum(bs_value * bs_dir)
FROM (SELECTBS_IDBS_PID LTRIM (SYS_CONNECT_BY_PATH (bs_name |) |) PATH
bs_valuebs_dir
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = )
group by SUBSTR (PATH INSTR (PATH | ) )
竟然有意外的收获原表中的数据流动资产是错的！！！也许是我对业务知识了解不够如果您知道原因还清指点
数据的最后一行是对所有原表第一层节点的叠加如果希望得到资产和负债的总计数据需要对节点顺序进行重新调整我的想法是建立一个表示汇总关系的逻辑的BS_LID 和
BS_LParID
