该表是指DSST_YB3,
父子值比方说
a是父值 a=100
b,c 是子值b=40,c=60
a包含b,c
DSST_YB3表结构如下:
create table DSST_YB3 (
NIAN CHAR(4) not null,
YUE CHAR(2) not null,
XM CHAR(30),
HC NUMBER(3) not null,
X1 NUMBER(18,4),
X2 NUMBER(18,4),
X3 NUMBER(18,4),
X4 NUMBER(18,4),
X5 NUMBER(18,4),
X6 NUMBER(18,4),
X7 NUMBER(18,4),
X8 NUMBER(18,4),
X9 NUMBER(18,4),
X10 NUMBER(18,4),
X11 NUMBER(18,4),
X12 NUMBER(18,4),
X13 NUMBER(18,4),
X14 NUMBER(18,4),
X15 NUMBER(18,4),
X16 NUMBER(18,4),
X17 NUMBER(18,4),
DANWEI VARCHAR(50) not null,
SALE_CODE VARCHAR(50),
SOUR_CODE VARCHAR(50),
constraint PK_DSST_YB3 primary key (NIAN, YUE, HC, DANWEI)
)
表DSSD_GENE结构如下:
create table DSSD_GENE (
DIME_VALU_CODE VARCHAR(50) not null,
DIME_CODE CHAR(5) not null,
DIME_VALU VARCHAR(50),
DIME_VALU_NAME VARCHAR(255),
DIME_VALU_REPO_NAME VARCHAR(255),
DIME_VALU_SERI NUMBER(8),
IF_TOP CHAR(1),
IF_BOTT CHAR(1),
DIME_VALU_LEVE NUMBER(8),
UP_DIME_VALU_CODE VARCHAR(50),
NAME_IN_FACT VARCHAR(50),
constraint PK_DSSD_GENE primary key (DIME_VALU_CODE),
constraint FK_DSSD_GEN_REF_21_DSSS_DIM foreign key (DIME_CODE)
references DSSS_DIME_TYPE (DIME_CODE)
);
父子值比方说
a是父值 a=100
b,c 是子值b=40,c=60
a包含b,c
DSST_YB3表结构如下:
create table DSST_YB3 (
NIAN CHAR(4) not null,
YUE CHAR(2) not null,
XM CHAR(30),
HC NUMBER(3) not null,
X1 NUMBER(18,4),
X2 NUMBER(18,4),
X3 NUMBER(18,4),
X4 NUMBER(18,4),
X5 NUMBER(18,4),
X6 NUMBER(18,4),
X7 NUMBER(18,4),
X8 NUMBER(18,4),
X9 NUMBER(18,4),
X10 NUMBER(18,4),
X11 NUMBER(18,4),
X12 NUMBER(18,4),
X13 NUMBER(18,4),
X14 NUMBER(18,4),
X15 NUMBER(18,4),
X16 NUMBER(18,4),
X17 NUMBER(18,4),
DANWEI VARCHAR(50) not null,
SALE_CODE VARCHAR(50),
SOUR_CODE VARCHAR(50),
constraint PK_DSST_YB3 primary key (NIAN, YUE, HC, DANWEI)
)
表DSSD_GENE结构如下:
create table DSSD_GENE (
DIME_VALU_CODE VARCHAR(50) not null,
DIME_CODE CHAR(5) not null,
DIME_VALU VARCHAR(50),
DIME_VALU_NAME VARCHAR(255),
DIME_VALU_REPO_NAME VARCHAR(255),
DIME_VALU_SERI NUMBER(8),
IF_TOP CHAR(1),
IF_BOTT CHAR(1),
DIME_VALU_LEVE NUMBER(8),
UP_DIME_VALU_CODE VARCHAR(50),
NAME_IN_FACT VARCHAR(50),
constraint PK_DSSD_GENE primary key (DIME_VALU_CODE),
constraint FK_DSSD_GEN_REF_21_DSSS_DIM foreign key (DIME_CODE)
references DSSS_DIME_TYPE (DIME_CODE)
);
DSST_YB3 存放所有的代码
DSSD_GENE 存放代码之间的父子关系。
但是一定会是两层吗?不是父亲就是孩子?
(select a.UP_DIME_VALU_CODE,a.DIME_VALU_CODE,b.danwei,to_char(b.HC) as XM,
b.X15 px15,nvl(c.X15,0) cx15
from DSSD_GENE a,DSST_YB3 b,DSST_YB3 c
where a.UP_DIME_VALU_CODE = b.SALE_CODE
and a.DIME_VALU_CODE = c.SALE_CODE
and a.IF_BOTT = '1') aa
group by aa.UP_DIME_VALU_CODE,aa.danwei,aa.XM;说实话,贴主你问题问也不明白,表结构并不单单指建表文件,还有你的设计思想。
而且我觉得就现在的表结构来猜想分析,也并不是很好。
所以如果这个语句通不过也是正常的。
表是这样比方说我dsst_yb3有9条记录。第1条是第2,3,5条记录的父记录。第1条记录的值应该等于
2,3,5条记录的和。第4条记录是第6,7,8条记录的父记录,第4条记录的值应该等于6,7,8条记录的和。第9条记录是第1条和第4条记录的父记录的子记录。第9条记录的值应该等于第1条和第4条记录的值的和。DSST_GENE表的DIME_VALU_CODE是和DSSD_YB3的SALE_CODE向对应的。up_dime_valu_code是dime_valu_code的父层代码。
DSST_yb3部分表结构如下:
danwei(单位) sale_code(类别) x15(值) hc(行次)
GDDW00000000004 SDFL0000010000100001 3695 10
GDDW00000000004 SDFL000001000010000100001 1563 20
GDDW00000000004 SDFL000001000010000100002 565 30
GDDW00000000005 SDFL0000010000100002 7568 40
GDDW00000000004 SDFL000001000010000100004 1459 50
GDDW00000000005 SDFL000001000010000200001 654 60
GDDW00000000005 SDFL000001000010000200002 5369 70
GDDW00000000005 SDFL000001000010000200003 895 80
GDDW00000000001 SDFL00000100001 11263 90
DSSD_GENE部分表结构如下:
dime_valu_code(子代码) up_dime_valu_code(父代码) if_bott
SDFL0000010000100001 SDFL00000100001 0
SDFL000001000010000100001 SDFL0000010000100001 1
SDFL000001000010000100002 SDFL0000010000100001 1
SDFL0000010000100002 SDFL00000100001 0
SDFL000001000010000100004 SDFL0000010000100001 1
SDFL000001000010000200001 SDFL0000010000100002 1
SDFL000001000010000200002 SDFL0000010000100002 1
SDFL000001000010000200003 SDFL0000010000100002 1
SDFL00000100001 SDFL000001 0
(if_bott(是否底层 “0”代表不是,“1”代表是))。
dsst_yb3部分表结构如下:
danwei(单位) sale_code(类别)
GDDW00000000004 SDFL0000010000100001
GDDW00000000004 SDFL000001000010000100001
GDDW00000000004 SDFL000001000010000100002
GDDW00000000005 SDFL0000010000100002
GDDW00000000004 SDFL000001000010000100004
GDDW00000000005 SDFL000001000010000200001
GDDW00000000005 SDFL000001000010000200002
GDDW00000000005 SDFL000001000010000200003
GDDW00000000001 SDFL00000100001 x15(值) hc(行次)
3695 10
1563 20
565 30
7568 40
1459 50
654 60
5369 70
895 80
11263 90
dssd_gene部分表结构如下:
dime_valu_code(子代码) up_dime_valu_code(父代码)
SDFL0000010000100001 SDFL00000100001 0
SDFL000001000010000100001 SDFL0000010000100001 1
SDFL000001000010000100002 SDFL0000010000100001 1
SDFL0000010000100002 SDFL00000100001 0
SDFL000001000010000100004 SDFL0000010000100001 1
SDFL000001000010000200001 SDFL0000010000100002 1
SDFL000001000010000200002 SDFL0000010000100002 1
SDFL000001000010000200003 SDFL0000010000100002 1
SDFL00000100001 SDFL000001 0
if_bott(代表是否底层如果是”0“就不是底层,如果是”1“就是底层)
0
1
1
0
1
1
1
1
0
dssd_gene部分表结构如下:
dime_valu_code(子代码) up_dime_valu_code(父代码)
SDFL0000010000100001 SDFL00000100001
SDFL000001000010000100001 SDFL0000010000100001
SDFL000001000010000100002 SDFL0000010000100001
SDFL0000010000100002 SDFL00000100001
SDFL000001000010000100004 SDFL0000010000100001
SDFL000001000010000200001 SDFL0000010000100002
SDFL000001000010000200002 SDFL0000010000100002
SDFL000001000010000200003 SDFL0000010000100002
SDFL00000100001 SDFL000001
if_bott(代表是否底层如果是”0“就不是底层,如果是”1“就是底层)
0
1
1
0
1
1
1
1
0
第一层是SDFL000001
第二层是SDFL00000100001
第三层是SDFL0000010000100001和SDFL0000010000100002
第四层是剩下的
那么你想看第几层的父-子之和的差呢?
from (select a.up_dime_valu_code,sum(nvl(b.x15,0)) cx15
from dssd_gene a,dsst_yb3 b
where a.dime_valu_code = b.sale_code
group by a.up_dime_valu_code) aa,dsst_yb3 bb
where aa.up_dime_valu_code = bb.sale_code;子查询表示计算每个up_dime_valu_code(父)的值,
为其子的x15之和。
由于up_dime_valu_code也是存放的sale_code,那么
对应到dsst_yb3也有个x15值。
两值之差为所求。
不知道理解的对不对?
有问题请提!
return number
as
cursor t_sor is
select dime_valu_code, if_bott
from DSSD_GENE
connect by prior dime_valu_code = up_dime_valu_code
start with up_dime_valu_code = p_sale_code;
v_num number;
v_sum number;
begin
for v_sor in t_sor loop
if if_bott<>'0' then
select sum(x15) into num from DSST_YB3 where sale_code=v_sor.dime_valu_code;
end if;
v_sum:=v_sum+num;
end loop;
return v_sum;
end;
/
select a.*,get_sum(sale_code) from DSST_YB3;
from (select b.danwei,a.up_dime_valu_code,sum(nvl(b.x13,0)) cx13
from dssd_gene a,dsst_yb2 b
where a.dime_valu_code = b.sale_code
group by a.up_dime_valu_code,b.danwei) aa,dsst_yb2 bb
where aa.up_dime_valu_code = bb.sale_code and aa.danwei = bb.danwei
order by danwei,hc;上次没加单位的分组,所以不对。
看了数据才知道,原来每个单位有同一套hc(行号)和sale_code