有表:
create table ORIGIN_TRAN
(
TRANID NUMBER(18) not null,
INACC VARCHAR2(20) not null,
TOTALAMT NUMBER(18) not null,
OUTACC VARCHAR2(20) not null,
STATUS CHAR(1) default '0' not null,
CHANNID VARCHAR2(4) not null,
)insert into origin_tran values(1001,'888881',10000,'999999','0','0001');
insert into origin_tran values(1002,'888882',20000,'999999','0','0001');
insert into origin_tran values(1003,'888883',30000,'999999','0','0001');
insert into origin_tran values(1004,'888884',40000,'999999','0','0001');
insert into origin_tran values(1005,'888885',50000,'999999','0','0001');create table SPLIT_TRAN
(
AUTOID NUMBER(16) not null,
ORGINTRANID NUMBER(18) not null,
AMOUNT NUMBER(18) not null,
STATUS CHAR(1) not null
)create table PAYCHANNEL
(
ID VARCHAR2(4) not null,
NAME VARCHAR2(30) not null,
MAXTRANAMT NUMBER(18)
)
insert into paychannel values('0001','A通道',10000);
insert into paychannel values('0002','B通道',3000);从origin_tran中,查找出CHANNID='0001'的,再从paychannel中,查找出MAXTRANAMT,根据它来拆分记录 写入到split_tran 中去。希望最后出来的split_tran中的结果是这样:
1 1001 10000 '0',
2 1002 10000 '0',
3 1002 10000 '0',4 1003 10000 '0',
5 1003 10000 '0',
6 1003 10000 '0',7 1004 10000 '0',
8 1004 10000 '0',
9 1004 10000 '0',
10 1004 10000 '0',
11 1005 10000 '0',
12 1005 10000 '0',
13 1005 10000 '0',
14 1005 10000 '0',
15 1005 10000 '0'
有没有高效一点的方法???
create table ORIGIN_TRAN
(
TRANID NUMBER(18) not null,
INACC VARCHAR2(20) not null,
TOTALAMT NUMBER(18) not null,
OUTACC VARCHAR2(20) not null,
STATUS CHAR(1) default '0' not null,
CHANNID VARCHAR2(4) not null,
)insert into origin_tran values(1001,'888881',10000,'999999','0','0001');
insert into origin_tran values(1002,'888882',20000,'999999','0','0001');
insert into origin_tran values(1003,'888883',30000,'999999','0','0001');
insert into origin_tran values(1004,'888884',40000,'999999','0','0001');
insert into origin_tran values(1005,'888885',50000,'999999','0','0001');create table SPLIT_TRAN
(
AUTOID NUMBER(16) not null,
ORGINTRANID NUMBER(18) not null,
AMOUNT NUMBER(18) not null,
STATUS CHAR(1) not null
)create table PAYCHANNEL
(
ID VARCHAR2(4) not null,
NAME VARCHAR2(30) not null,
MAXTRANAMT NUMBER(18)
)
insert into paychannel values('0001','A通道',10000);
insert into paychannel values('0002','B通道',3000);从origin_tran中,查找出CHANNID='0001'的,再从paychannel中,查找出MAXTRANAMT,根据它来拆分记录 写入到split_tran 中去。希望最后出来的split_tran中的结果是这样:
1 1001 10000 '0',
2 1002 10000 '0',
3 1002 10000 '0',4 1003 10000 '0',
5 1003 10000 '0',
6 1003 10000 '0',7 1004 10000 '0',
8 1004 10000 '0',
9 1004 10000 '0',
10 1004 10000 '0',
11 1005 10000 '0',
12 1005 10000 '0',
13 1005 10000 '0',
14 1005 10000 '0',
15 1005 10000 '0'
有没有高效一点的方法???
---- 测试数据
WITH tmp AS
(
SELECT 1 ID, 21000 TOTAL_VALUES , 10000 MAX_VALUES FROM dual UNION ALL
SELECT 2 ID, 20000 TOTAL_VALUES , 10000 MAX_VALUES FROM dual UNION ALL
SELECT 3 ID, 5000 TOTAL_VALUES , 10000 MAX_VALUES FROM dual
)
---- 查询SQL SELECT ID,"VALUES"
FROM (
-- 抽取一 整除的部分(21000 = 10000,10000) 剩余 1000 留在抽取二 中查询 + 合并
SELECT DISTINCT ID
, CASE WHEN MAX_VALUES > TOTAL_VALUES
THEN TOTAL_VALUES
ELSE MAX_VALUES END "VALUES"
, LEVEL
FROM tmp
WHERE TOTAL_VALUES > MAX_VALUES
CONNECT BY LEVEL <= trunc(TOTAL_VALUES / MAX_VALUES) UNION ALL -- 抽取二 不能整除的部分(21000 = 1000)、以及 不足额(5000 < 分隔标准 10000)
SELECT ID
, MOD(TOTAL_VALUES , MAX_VALUES) "VALUES"
,-9
FROM tmp
WHERE MOD(TOTAL_VALUES , MAX_VALUES) > 0 -- 不能整除的部分(取余)
OR MAX_VALUES > TOTAL_VALUES -- 不足额的部分
)
ORDER BY 1,2;
希望也对大家有用。
http://www.itpub.net/thread-1214858-1-1.html