建表语句:
create table PJ_BUSINESSDETAIL
(
GUID NUMBER(20) not null,
F_BUMAGUID NUMBER(20) not null,
F_BITYCODE VARCHAR2(3) not null,
STARTNO VARCHAR2(10),
ENDNO VARCHAR2(10),
SHEETS NUMBER(10),
PRESSPRICE NUMBER(16,4),
WHOLESALEPRICE NUMBER(16,4),
RETAILPRICE NUMBER(16,4),
PRICE NUMBER(16,4),
PERMONEY NUMBER(16,4),
SYN_FLAG CHAR(1)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column PJ_BUSINESSDETAIL.GUID
is '主键(业务记录序号)';
comment on column PJ_BUSINESSDETAIL.F_BUMAGUID
is '明细所属业务主记录(引用PJ_BusinessMain表)';
comment on column PJ_BUSINESSDETAIL.F_BITYCODE
is '票据种类编码(引用PJ_BillType表)';
comment on column PJ_BUSINESSDETAIL.STARTNO
is '起始号码';
comment on column PJ_BUSINESSDETAIL.ENDNO
is '终止号码';
comment on column PJ_BUSINESSDETAIL.SHEETS
is '份数';
comment on column PJ_BUSINESSDETAIL.PRESSPRICE
is '票据印刷成本价';
comment on column PJ_BUSINESSDETAIL.WHOLESALEPRICE
is '票据工本费收费标准';
comment on column PJ_BUSINESSDETAIL.RETAILPRICE
is '票据工本费收费标准';
comment on column PJ_BUSINESSDETAIL.PRICE
is '本条适用价格';
comment on column PJ_BUSINESSDETAIL.PERMONEY
is '本条金额';
comment on column PJ_BUSINESSDETAIL.SYN_FLAG
is '数据库同步标志 0-应用添加 1-同步添加';
-- Create/Recreate primary, unique and foreign key constraints
alter table PJ_BUSINESSDETAIL
add primary key (GUID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table PJ_BUSINESSDETAIL
add constraint F_BITYCODE_BD foreign key (F_BITYCODE)
references PJ_BILLTYPE (BITYCODE);
alter table PJ_BUSINESSDETAIL
add constraint F_BUMAGUID_BD foreign key (F_BUMAGUID)
references PJ_BUSINESSMAIN (GUID);
-- Create/Recreate indexes
create index IDX_PJ_BUSINESSDETAIL2 on PJ_BUSINESSDETAIL (F_BUMAGUID)
tablespace FSZG
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);原内容:
select * from pj_businessdetail a where a.f_bumaguid = '3304210000000198'
序号 guid f_bumaguid f_bitycode startno endno
1 3304210000000281 3304210000000198 742 0001768591 0001769290
2 3304210000000282 3304210000000198 741 0000051376 0000051400
3 3304210000000283 3304210000000198 743 0002869276 0002869300需要的结果:
序号 guid f_bumaguid f_bitycode startno endno
1 3304210000000281 3304210000000198 742 0001768591 0001768591
2 3304210000000281 3304210000000198 742 0001768592 0001768592
3 3304210000000281 3304210000000198 742 0001768593 0001768593
4 3304210000000281 3304210000000198 742 0001769290 0001769290
5 3304210000000282 3304210000000198 741 0000051376 0000051400
6 3304210000000283 3304210000000198 743 0002869276 0002869300
即根据本条记录startno和endno之间的号码段,有几个号码就把他分成几条记录,别的字段内容不变。
create table PJ_BUSINESSDETAIL
(
GUID NUMBER(20) not null,
F_BUMAGUID NUMBER(20) not null,
F_BITYCODE VARCHAR2(3) not null,
STARTNO VARCHAR2(10),
ENDNO VARCHAR2(10),
SHEETS NUMBER(10),
PRESSPRICE NUMBER(16,4),
WHOLESALEPRICE NUMBER(16,4),
RETAILPRICE NUMBER(16,4),
PRICE NUMBER(16,4),
PERMONEY NUMBER(16,4),
SYN_FLAG CHAR(1)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column PJ_BUSINESSDETAIL.GUID
is '主键(业务记录序号)';
comment on column PJ_BUSINESSDETAIL.F_BUMAGUID
is '明细所属业务主记录(引用PJ_BusinessMain表)';
comment on column PJ_BUSINESSDETAIL.F_BITYCODE
is '票据种类编码(引用PJ_BillType表)';
comment on column PJ_BUSINESSDETAIL.STARTNO
is '起始号码';
comment on column PJ_BUSINESSDETAIL.ENDNO
is '终止号码';
comment on column PJ_BUSINESSDETAIL.SHEETS
is '份数';
comment on column PJ_BUSINESSDETAIL.PRESSPRICE
is '票据印刷成本价';
comment on column PJ_BUSINESSDETAIL.WHOLESALEPRICE
is '票据工本费收费标准';
comment on column PJ_BUSINESSDETAIL.RETAILPRICE
is '票据工本费收费标准';
comment on column PJ_BUSINESSDETAIL.PRICE
is '本条适用价格';
comment on column PJ_BUSINESSDETAIL.PERMONEY
is '本条金额';
comment on column PJ_BUSINESSDETAIL.SYN_FLAG
is '数据库同步标志 0-应用添加 1-同步添加';
-- Create/Recreate primary, unique and foreign key constraints
alter table PJ_BUSINESSDETAIL
add primary key (GUID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table PJ_BUSINESSDETAIL
add constraint F_BITYCODE_BD foreign key (F_BITYCODE)
references PJ_BILLTYPE (BITYCODE);
alter table PJ_BUSINESSDETAIL
add constraint F_BUMAGUID_BD foreign key (F_BUMAGUID)
references PJ_BUSINESSMAIN (GUID);
-- Create/Recreate indexes
create index IDX_PJ_BUSINESSDETAIL2 on PJ_BUSINESSDETAIL (F_BUMAGUID)
tablespace FSZG
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);原内容:
select * from pj_businessdetail a where a.f_bumaguid = '3304210000000198'
序号 guid f_bumaguid f_bitycode startno endno
1 3304210000000281 3304210000000198 742 0001768591 0001769290
2 3304210000000282 3304210000000198 741 0000051376 0000051400
3 3304210000000283 3304210000000198 743 0002869276 0002869300需要的结果:
序号 guid f_bumaguid f_bitycode startno endno
1 3304210000000281 3304210000000198 742 0001768591 0001768591
2 3304210000000281 3304210000000198 742 0001768592 0001768592
3 3304210000000281 3304210000000198 742 0001768593 0001768593
4 3304210000000281 3304210000000198 742 0001769290 0001769290
5 3304210000000282 3304210000000198 741 0000051376 0000051400
6 3304210000000283 3304210000000198 743 0002869276 0002869300
即根据本条记录startno和endno之间的号码段,有几个号码就把他分成几条记录,别的字段内容不变。
加个控制 直到MAX(startno)=0001769290
FROM pj_businessdetail a
WHERE a.f_bumaguid = '3304210000000198' )
SELECT a.*,lpad(a.startno+r,10,0) l_startno,lpad(a.startno+r,10,0) l_endno
FROM TX a,(SELECT ROWNUM-1 r FROM dual CONNECT BY ROWNUM<=(SELECT MAX(cnt)+1 FROM TX)) b
WHERE a.cnt>=b.r
FROM pj_businessdetail a
WHERE a.f_bumaguid = '3304210000000198' )
SELECT a.*,lpad(a.startno+r,10,0) l_startno,lpad(a.startno+r,10,0) l_endno
FROM TX a,(SELECT ROWNUM-1 r FROM dual CONNECT BY ROWNUM<=(SELECT MAX(cnt)+1 FROM TX)) b
WHERE a.cnt>=b.r
FROM (SELECT TO_CHAR(TO_NUMBER(STARTNO) + RN - 1, 'fm0000000000') DD,
CK.F_BUMAGUID,
CK.F_BITYCODE,
GUID
FROM PJ_BUSINESSDETAIL CK,
(SELECT ROWNUM RN
FROM DUAL
CONNECT BY ROWNUM <=
(SELECT MAX(TO_NUMBER(ENDNO) - TO_NUMBER(STARTNO) + 1)
FROM PJ_BUSINESSDETAIL))
WHERE TO_NUMBER(STARTNO) + RN - 1 BETWEEN TO_NUMBER(STARTNO) AND
TO_NUMBER(ENDNO))
ORDER BY GUID, STARTNO;
输出:
3304210000000281 3304210000000198 742 0001768591 0001768591
3304210000000281 3304210000000198 742 0001768592 0001768592
3304210000000281 3304210000000198 742 0001768593 0001768593
3304210000000281 3304210000000198 742 0001768594 0001768594
3304210000000281 3304210000000198 742 0001768595 0001768595
3304210000000281 3304210000000198 742 0001768596 0001768596
3304210000000281 3304210000000198 742 0001768597 0001768597
......