表 A
ID MONTH AMOUNT
-----------------
1 200701 100
1 200702 200
1 200703 300
2 200701 200
2 200702 300
2 200703 400表B
ID SUM_AMOUNT
--------------
1 400
2 600表A与表B通过ID关联。
即将表B的SUM_AMOUNT分摊到表A的明细上去。
通过一条SQL语句得到如下结果。
ID MONTH AMOUNT AMOUNT1
----------------------------
1 200701 100 100
1 200702 200 200
1 200703 300 100
2 200701 200 200
2 200702 300 300
2 200703 400 100
ID MONTH AMOUNT
-----------------
1 200701 100
1 200702 200
1 200703 300
2 200701 200
2 200702 300
2 200703 400表B
ID SUM_AMOUNT
--------------
1 400
2 600表A与表B通过ID关联。
即将表B的SUM_AMOUNT分摊到表A的明细上去。
通过一条SQL语句得到如下结果。
ID MONTH AMOUNT AMOUNT1
----------------------------
1 200701 100 100
1 200702 200 200
1 200703 300 100
2 200701 200 200
2 200702 300 300
2 200703 400 100
create table a
(
id number,
month char(10),
amount number
);insert into a
select 1,01,100 from dual
union all
select 1,02,200 from dual
union all
select 1,03,300 from dual
union all
select 2,01,200 from dual
union all
select 2,02,300 from dual
union all
select 2,03,400 from dual;create table b
(
id number,
sum_amount number
);
insert into b
select 1,400 from dual
union all
select 2,600 from dual;
select a1.id,a1.month,a1.amount,
decode(SIGN(b.sum_amount-a1.jj),1,a1.amount,abs(a1.jj-a1.amount-b.sum_amount)) amount1
from
(
select id,month,amount,
sum(amount)over(partition by id order by month) as jj
from a
order by id,month desc
) a1
left join b on (a1.id=b.id);
IDMONTH AMOUNT AMOUNT1
----------------------------------------
11 100 100
12 200 200
13 300 100
21 200 200
22 300 300
23 400 100
可是表A的记录数不是固定的啊,ID有N个,每个ID也对应的N行数据
表 A
ID MONTH AMOUNT
-----------------
1 200701 100
1 200702 200
1 200703 300
1 200704 300表B
ID SUM_AMOUNT
--------------
1 400 就会有问题了也就是说,SUM_AMOUNT 一定在足够分配
From A T1 Left Outer Join B T2
ON T1.ID = T2.ID基本上和wdswcy的想法是一样的.没有考虑负数的情况。
谢谢你的回答,我想问一下,你那个SQL语句中的 sum(amount)over(partition by id order by month)
里面的OVER partition 代表什么意思?谢谢
就是按ID进行分组,将amount累加起来
select id,month,amount,
sum(amount)over(partition by id order by month) as jj
from a结果是这样的
ID MONTH AMOUNT JJ
---------- ---------- ---------- ----------
1 1 100 100
1 2 200 300
1 3 300 600
2 1 200 200
2 2 300 500
2 3 400 900
特别谢谢 wdswcy 问题解决了。结贴