根据AA_TEMP表整理出FB_P_PRE_MAIN表,表结构如下:create table AA_TEMP
(
DIV_NAME VARCHAR2(200),
XM_CODE VARCHAR2(200),
XM_NAME VARCHAR2(400),
PRO_CODE VARCHAR2(200),
PRO_NAME VARCHAR2(400),
PX NUMBER
);create table FB_P_PRE_MAIN
(
SET_YEAR NUMBER(4) not null,
PRJ_ID VARCHAR2(20) not null,
PRJ_CODE VARCHAR2(60) not null,
PRJ_NAME VARCHAR2(120) not null,
PRJ_FNAME VARCHAR2(120),
END_FLAG NUMBER(1) default '0',
IS_DELETED NUMBER(1) default '0',
PAR_CODE VARCHAR2(60),
LEVEL_NUM NUMBER(2),
RG_CODE VARCHAR2(60),
LAST_VER VARCHAR2(60),
REMARK VARCHAR2(4000),
DIVNAME VARCHAR2(200)
)
需求:
1 FB_P_PRE_MAIN.PRJ_ID为主键,规则:当END_FLAG为0时,PRJ_ID长度为4,如:0001,0002,0003.....;当END_FLAG为1时,PRJ_ID长度为8,如:00010001,00010002,00020001.....2 当END_FLAG为0,FB_P_PRE_MAIN.PRJ_CODE,每个DIV_NAME的PRJ_COD都是从01开始.如:001001有3个XM_NAME,那么在FB_P_PRE_MAIN表的PRJ_CODE为,01,02,03 当END_FLAG为1,FB_P_PRE_MAIN.PRJ_CODE,每个DIV_NAME的PRJ_COD都是从0101开始.如:001001有3个XM_NAME,每个XM_NAME有3个PRO_NAME,那么在FB_P_PRE_MAIN表的PRJ_CODE为,0101,0102,0103,0201,0202,0203,0301,0302,0303.....3 当END_FLAG为0,FB_P_PRE_MAIN.PRJ_NAME为AA_TEMP.XM_NAME,PRJ_FNAME为AA_TEMP.XM_NAME;当END_FLAG为1,FB_P_PRE_MAIN.PRJ_NAME为AA_TEMP.PRO_NAME,PRJ_FNAME为AA_TEMP.XM_NAME_AA_TEMP.PRO_NAME4 SET_YEAR默认为2010;IS_DELETED默认为0;RG_CODE默认为440304;LEVEL_NUM:当END_FLAG为0时LEVEL_NUM为1,当END_FLAG为1时LEVEL_NUM为2;5 FB_P_PRE_MAIN.DIVNAME等于AA_TEMP.DIV_NAME6 PAR_COD ,LAST_VER,REMARK 为空.
(
DIV_NAME VARCHAR2(200),
XM_CODE VARCHAR2(200),
XM_NAME VARCHAR2(400),
PRO_CODE VARCHAR2(200),
PRO_NAME VARCHAR2(400),
PX NUMBER
);create table FB_P_PRE_MAIN
(
SET_YEAR NUMBER(4) not null,
PRJ_ID VARCHAR2(20) not null,
PRJ_CODE VARCHAR2(60) not null,
PRJ_NAME VARCHAR2(120) not null,
PRJ_FNAME VARCHAR2(120),
END_FLAG NUMBER(1) default '0',
IS_DELETED NUMBER(1) default '0',
PAR_CODE VARCHAR2(60),
LEVEL_NUM NUMBER(2),
RG_CODE VARCHAR2(60),
LAST_VER VARCHAR2(60),
REMARK VARCHAR2(4000),
DIVNAME VARCHAR2(200)
)
需求:
1 FB_P_PRE_MAIN.PRJ_ID为主键,规则:当END_FLAG为0时,PRJ_ID长度为4,如:0001,0002,0003.....;当END_FLAG为1时,PRJ_ID长度为8,如:00010001,00010002,00020001.....2 当END_FLAG为0,FB_P_PRE_MAIN.PRJ_CODE,每个DIV_NAME的PRJ_COD都是从01开始.如:001001有3个XM_NAME,那么在FB_P_PRE_MAIN表的PRJ_CODE为,01,02,03 当END_FLAG为1,FB_P_PRE_MAIN.PRJ_CODE,每个DIV_NAME的PRJ_COD都是从0101开始.如:001001有3个XM_NAME,每个XM_NAME有3个PRO_NAME,那么在FB_P_PRE_MAIN表的PRJ_CODE为,0101,0102,0103,0201,0202,0203,0301,0302,0303.....3 当END_FLAG为0,FB_P_PRE_MAIN.PRJ_NAME为AA_TEMP.XM_NAME,PRJ_FNAME为AA_TEMP.XM_NAME;当END_FLAG为1,FB_P_PRE_MAIN.PRJ_NAME为AA_TEMP.PRO_NAME,PRJ_FNAME为AA_TEMP.XM_NAME_AA_TEMP.PRO_NAME4 SET_YEAR默认为2010;IS_DELETED默认为0;RG_CODE默认为440304;LEVEL_NUM:当END_FLAG为0时LEVEL_NUM为1,当END_FLAG为1时LEVEL_NUM为2;5 FB_P_PRE_MAIN.DIVNAME等于AA_TEMP.DIV_NAME6 PAR_COD ,LAST_VER,REMARK 为空.
values ('001001', '01', '项目分类1', '01', '项目名称dfdskxsd', 1);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '01', '项目分类2', '01', '项目名称dfdskfdfdf', 2);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '', '', '02', '项目名称dfdskseff', 3);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '01', '项目分类3', '01', '项目名称dfdskdfs', 4);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '', '', '02', '项目名称dfdskxds', 5);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '01', '项目分类4', '01', '项目名称dfdsk6tr', 6);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '01', '项目分类5', '01', '项目名称dfdskjjy', 7);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '', '', '02', '项目名称dfdskrtw', 8);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '01', '项目分类6', '01', '项目名称dfdskrt', 9);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('001002', '01', '项目分类7', '01', '项目名称dfdskgf', 10);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '01', '项目分类8', '01', '项目名称dfdsk34', 11);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '01', '项目分类9', '01', '项目名称dfdsk43', 12);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '01', '项目分类10', '01', '项目名称dfdfg', 13);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '', '', '02', '项目名称dfdes', 14);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '', '', '03', '项目名称dsdsk', 15);insert into aa_temp (DIV_NAME, XM_CODE, XM_NAME, PRO_CODE, PRO_NAME, PX)
values ('', '', '', '04', '项目名称dfdssd', 16);
values (2010, '0001', '01', '项目分类1', '项目分类1', 0, 0, '', 1, '440304', '', '', '001001');insert into FB_P_PRE_MAIN (SET_YEAR, PRJ_ID, PRJ_CODE, PRJ_NAME, PRJ_FNAME, END_FLAG, IS_DELETED, PAR_CODE, LEVEL_NUM, RG_CODE, LAST_VER, REMARK, DIVNAME)
values (2010, '00010001', '0101', '项目名称dfdskxsd', '项目分类1_项目名称dfdskxsd', 1, 0, '', 2, '440304', '', '', '001001');insert into FB_P_PRE_MAIN (SET_YEAR, PRJ_ID, PRJ_CODE, PRJ_NAME, PRJ_FNAME, END_FLAG, IS_DELETED, PAR_CODE, LEVEL_NUM, RG_CODE, LAST_VER, REMARK, DIVNAME)
values (2010, '0002', '02', '项目分类2', '项目分类2', 0, 0, '', 1, '440304', '', '', '001001');insert into FB_P_PRE_MAIN (SET_YEAR, PRJ_ID, PRJ_CODE, PRJ_NAME, PRJ_FNAME, END_FLAG, IS_DELETED, PAR_CODE, LEVEL_NUM, RG_CODE, LAST_VER, REMARK, DIVNAME)
values (2010, '00020001', '0201', '项目名称dfdskfdfdf', '项目分类2_项目名称dfdskfdfdf', 1, 0, '', 2, '440304', '', '', '001001');insert into FB_P_PRE_MAIN (SET_YEAR, PRJ_ID, PRJ_CODE, PRJ_NAME, PRJ_FNAME, END_FLAG, IS_DELETED, PAR_CODE, LEVEL_NUM, RG_CODE, LAST_VER, REMARK, DIVNAME)
values (2010, '00020002', '0202', '项目名称dfdskseff', '项目分类2_项目名称dfdskseff', 1, 0, '', 2, '440304', '', '', '001001');
http://hi.csdn.net/attachment/200910/25/35597_12564537658S5h.jpg
AA_TEMP.XM_NAME 在FB_P_PRE_MAIN存储时END_FLAG为0;AA_TEMP.PRO_NAME 在FB_P_PRE_MAIN存储时END_FLAG为1;再看看我抓的图就能发现它规律
select a.*,connect_by_root div_name div_name1 from(
select div_name,connect_by_root xm_code xm_code,
connect_by_root xm_name xm_name,
pro_code,pro_name,px
from aa_temp
start with xm_code is not null
connect by prior px=px-1 and xm_code is null)a
start with div_name is not null
connect by px=prior px+1 and div_name is null)
select '2010' set_year,to_char(row_number()over(order by xm_name),'0000')prj_id,
to_char(row_number()over(partition by div_name1 order by xm_name),'00')prj_code,
xm_name prj_name,xm_name prj_fname,0 end_flag,
null par_code,1 level_num,'440304'rg_code,null last_ver,
null re,div_name1 div_name
from tt
where pro_code='01'
union all
select '2010',
to_char(dense_rank()over(order by xm_name),'0000')||'00'||pro_code,
to_char(row_number()over(partition by div_name1 order by xm_name),'00')||pro_code,
pro_name,xm_name||'_'||pro_name,1,null,2,'440304',null,
null,div_name1
from tt
order by 1,2SET_YEAR PRJ_ID PRJ_CODE PRJ_NAME PRJ_FNAME END_FLAG PAR_CODE LEVEL_NUM RG_CODE LAST_VER REMARK DIV_NAME
2010 0001 01 项目分类1 项目分类1 0 1 440304 001001
2010 00010001 0101 项目名称dfdskxsd 项目分类1_项目名称dfdskxsd 1 2 440304 001001
2010 0002 01 项目分类10 项目分类10 0 1 440304 001002
2010 00020001 0101 项目名称dfdfg 项目分类10_项目名称dfdfg 1 2 440304 001002
2010 00020002 0202 项目名称dfdes 项目分类10_项目名称dfdes 1 2 440304 001002
2010 00020003 0303 项目名称dsdsk 项目分类10_项目名称dsdsk 1 2 440304 001002
2010 00020004 0404 项目名称dfdssd 项目分类10_项目名称dfdssd 1 2 440304 001002
2010 0003 02 项目分类2 项目分类2 0 1 440304 001001
2010 00030001 0201 项目名称dfdskfdfdf 项目分类2_项目名称dfdskfdfdf 1 2 440304 001001
2010 00030002 0302 项目名称dfdskseff 项目分类2_项目名称dfdskseff 1 2 440304 001001
2010 0004 03 项目分类3 项目分类3 0 1 440304 001001
2010 00040001 0401 项目名称dfdskdfs 项目分类3_项目名称dfdskdfs 1 2 440304 001001
2010 00040002 0502 项目名称dfdskxds 项目分类3_项目名称dfdskxds 1 2 440304 001001
2010 0005 04 项目分类4 项目分类4 0 1 440304 001001
2010 00050001 0601 项目名称dfdsk6tr 项目分类4_项目名称dfdsk6tr 1 2 440304 001001
2010 0006 05 项目分类5 项目分类5 0 1 440304 001001
2010 00060001 0701 项目名称dfdskjjy 项目分类5_项目名称dfdskjjy 1 2 440304 001001
2010 00060002 0802 项目名称dfdskrtw 项目分类5_项目名称dfdskrtw 1 2 440304 001001
2010 0007 06 项目分类6 项目分类6 0 1 440304 001001
2010 00070001 0901 项目名称dfdskrt 项目分类6_项目名称dfdskrt 1 2 440304 001001
2010 0008 02 项目分类7 项目分类7 0 1 440304 001002
2010 00080001 0501 项目名称dfdskgf 项目分类7_项目名称dfdskgf 1 2 440304 001002
2010 0009 03 项目分类8 项目分类8 0 1 440304 001002
2010 00090001 0601 项目名称dfdsk34 项目分类8_项目名称dfdsk34 1 2 440304 001002
2010 0010 04 项目分类9 项目分类9 0 1 440304 001002
2010 00100001 0701 项目名称dfdsk43 项目分类9_项目名称dfdsk43 1 2 440304 001002
xm_code和xm_name不是对应的吗?如果是的话可以把order by xm_name改成order by xm_code
或者改成order by length(xm_name),xm_name
with tt as(
select a.*,connect_by_root div_name div_name1 from(
select div_name,connect_by_root xm_code xm_code,
connect_by_root xm_name xm_name,
pro_code,pro_name,px
from aa_temp
start with xm_code is not null
connect by prior px=px-1 and xm_code is null)a
start with div_name is not null
connect by px=prior px+1 and div_name is null)
select '2010' set_year,to_char(row_number()over(order by length(xm_name),xm_name),'0000')prj_id,
to_char(row_number()over(partition by div_name1 order by length(xm_name),xm_name),'00')prj_code,
xm_name prj_name,xm_name prj_fname,0 end_flag,
null par_code,1 level_num,'440304'rg_code,null last_ver,
null re,div_name1 div_name
from tt
where pro_code='01'
union all
select '2010',
to_char(dense_rank()over(order by length(xm_name),xm_name),'0000')||'00'||pro_code,
to_char(dense_rank()over(partition by div_name1 order by length(xm_name),xm_name),'00')||pro_code,
pro_name,xm_name||'_'||pro_name,1,null,2,'440304',null,
null,div_name1
from tt
order by 1,2SET_YEAR PRJ_ID PRJ_CODE PRJ_NAME PRJ_FNAME END_FLAG PAR_CODE LEVEL_NUM RG_CODE LAST_VER REMARK DIV_NAME
2010 0001 01 项目分类1 项目分类1 0 1 440304 001001
2010 00010001 0101 项目名称dfdskxsd 项目分类1_项目名称dfdskxsd 1 2 440304 001001
2010 0002 02 项目分类2 项目分类2 0 1 440304 001001
2010 00020001 0201 项目名称dfdskfdfdf 项目分类2_项目名称dfdskfdfdf 1 2 440304 001001
2010 00020002 0202 项目名称dfdskseff 项目分类2_项目名称dfdskseff 1 2 440304 001001
2010 0003 03 项目分类3 项目分类3 0 1 440304 001001
2010 00030001 0301 项目名称dfdskdfs 项目分类3_项目名称dfdskdfs 1 2 440304 001001
2010 00030002 0302 项目名称dfdskxds 项目分类3_项目名称dfdskxds 1 2 440304 001001
2010 0004 04 项目分类4 项目分类4 0 1 440304 001001
2010 00040001 0401 项目名称dfdsk6tr 项目分类4_项目名称dfdsk6tr 1 2 440304 001001
2010 0005 05 项目分类5 项目分类5 0 1 440304 001001
2010 00050001 0501 项目名称dfdskjjy 项目分类5_项目名称dfdskjjy 1 2 440304 001001
2010 00050002 0502 项目名称dfdskrtw 项目分类5_项目名称dfdskrtw 1 2 440304 001001
2010 0006 06 项目分类6 项目分类6 0 1 440304 001001
2010 00060001 0601 项目名称dfdskrt 项目分类6_项目名称dfdskrt 1 2 440304 001001
2010 0007 01 项目分类7 项目分类7 0 1 440304 001002
2010 00070001 0101 项目名称dfdskgf 项目分类7_项目名称dfdskgf 1 2 440304 001002
2010 0008 02 项目分类8 项目分类8 0 1 440304 001002
2010 00080001 0201 项目名称dfdsk34 项目分类8_项目名称dfdsk34 1 2 440304 001002
2010 0009 03 项目分类9 项目分类9 0 1 440304 001002
2010 00090001 0301 项目名称dfdsk43 项目分类9_项目名称dfdsk43 1 2 440304 001002
2010 0010 04 项目分类10 项目分类10 0 1 440304 001002
2010 00100001 0401 项目名称dfdfg 项目分类10_项目名称dfdfg 1 2 440304 001002
2010 00100002 0402 项目名称dfdes 项目分类10_项目名称dfdes 1 2 440304 001002
2010 00100003 0403 项目名称dsdsk 项目分类10_项目名称dsdsk 1 2 440304 001002
2010 00100004 0404 项目名称dfdssd 项目分类10_项目名称dfdssd 1 2 440304 001002
select a.*,max(div_name)over(partition by rownum-level) div_name1 from(
select div_name,max(xm_code)over(partition by rownum-level) xm_code,
max(xm_name)over(partition by rownum-level) xm_name,
pro_code,pro_name,px
from aa_temp
start with xm_code is not null
connect by prior px=px-1 and xm_code is null)a
start with div_name is not null
connect by px=prior px+1 and div_name is null)
select '2010' set_year,to_char(row_number()over(order by length(xm_name),xm_name),'0000')prj_id,
to_char(row_number()over(partition by div_name1 order by length(xm_name),xm_name),'00')prj_code,
xm_name prj_name,xm_name prj_fname,0 end_flag,
null par_code,1 level_num,'440304'rg_code,null last_ver,
null re,div_name1 div_name
from tt
where pro_code='01'
union all
select '2010',
to_char(dense_rank()over(order by length(xm_name),xm_name),'0000')||'00'||pro_code,
to_char(dense_rank()over(partition by div_name1 order by length(xm_name),xm_name),'00')||pro_code,
pro_name,xm_name||'_'||pro_name,1,null,2,'440304',null,
null,div_name1
from tt
order by 1,2
不一定都为'01',如:div_name为 003001的 xm_code 有01,02,03这种情况改怎么处理?
那么不同的项目分类用什么来标识?
对order by进行下优化
with tt as(
select a.*,max(div_name)over(partition by rownum-level) div_name1 from(
select div_name,max(xm_code)over(partition by rownum-level) xm_code,
max(xm_name)over(partition by rownum-level) xm_name,
pro_code,pro_name,px,rownum-level flag
from aa_temp
start with xm_code is not null
connect by prior px=px-1 and xm_code is null)a
start with div_name is not null
connect by px=prior px+1 and div_name is null)
select '2010' set_year,to_char(row_number()over(order by flag),'0000')prj_id,
to_char(row_number()over(partition by div_name1 order by flag),'00')prj_code,
xm_name prj_name,xm_name prj_fname,0 end_flag,
null par_code,1 level_num,'440304'rg_code,null last_ver,
null re,div_name1 div_name
from tt
where pro_code='01'
union all
select '2010',
to_char(dense_rank()over(order by flag),'0000')||'00'||pro_code,
to_char(dense_rank()over(partition by div_name1 order by flag),'00')||pro_code,
pro_name,xm_name||'_'||pro_name,1,null,2,'440304',null,
null,div_name1
from tt
order by 1,2
你看下测试结果