有两张表:
A表(客户欠款表)
Ccode Produce_date Money
1 2010-9-30 10000
1 2010-10-8 3000
1 2010-10-9 5000
2 2010-9-30 2000
2 2010-10-8 3000
2 2010-10-9 1000
B表(客户付款表)
Ccode produce_date Money
1 2010-10-17 5000
1 2010-10-19 3000
2 2010-10-15 3000想得到如下结果
Ccode produce_date(欠款日期) money(欠款金额) produce_date(付款日期) money(付款金额)
1 2010-9-30 10000 2010-10-17 5000
1 2010-9-30 10000 2010-10-19 3000
1 2010-10-8 3000 0
2 2010-9-30 2000 2010-10-15 2000
2 2010-10-8 3000 2010-10-15 1000
2 2010-10-9 1000 0
A表(客户欠款表)
Ccode Produce_date Money
1 2010-9-30 10000
1 2010-10-8 3000
1 2010-10-9 5000
2 2010-9-30 2000
2 2010-10-8 3000
2 2010-10-9 1000
B表(客户付款表)
Ccode produce_date Money
1 2010-10-17 5000
1 2010-10-19 3000
2 2010-10-15 3000想得到如下结果
Ccode produce_date(欠款日期) money(欠款金额) produce_date(付款日期) money(付款金额)
1 2010-9-30 10000 2010-10-17 5000
1 2010-9-30 10000 2010-10-19 3000
1 2010-10-8 3000 0
2 2010-9-30 2000 2010-10-15 2000
2 2010-10-8 3000 2010-10-15 1000
2 2010-10-9 1000 0
比如说 一个客户截止到2010-9-30 欠 10000,2010-10-8 又欠 3000 2010-10-9 又欠5000,客户在2010-10-17和2010-10-19 分别回款回了5000和3000。此时的回款必须先填补最早的欠款,如此客户最早的欠款是在2010-9-30,所以客户的两次回款就去填补2010-9-30的欠款 所以产生以下记录
Ccode produce_date(欠款日期) money(欠款金额) produce_date(付款日期) money(付款金额)
1 2010-9-30 10000 2010-10-17 5000
1 2010-9-30 10000 2010-10-19 3000
1 2010-10-8 3000 0
1 2010-10-9 5000 0
from
(select id=row_number()over(partition by code order by produce_date asc),* from 客户欠款表) t1
left join
(select id=row_number()over(partition by code order by produce_date asc),* from 客户付款表) t2
on t1.id=t2.id and t1.code=t2.code