上次问过的问题，递减勾对

Question

有两个表
发货表
客户id   发货时间             发货金额
1         2011-01-01            3
1         2011-01-02            12
1         2011-01-03            32
1         2011-01-04            43
1         2011-01-04            20
2         2011-01-01            12
2         2011-01-01            43
3         2011-01-01            23回款表
客户id（可以保证唯一）        回款金额
1                               50
2                               30
发货表讲述的时候 什么时间 那个单位发了多少钱的货，回款表描述的是每个单位回了多少的款，怎样查出每个客户回款勾对完发货金额，剩余没有勾对完的发货金额（按时间先后勾对）。比如上面的数据查出来是如下的结果集
客户id     发货时间       发货金额
1         2011-01-04            40   // 客户1 回款50 - 3 -12 -32 = 3元，再勾对43时已不足，就把剩下的查出来 40-3 = 40
1         2011-01-04            20 
2         2011-01-01            43   
3         2011-01-01            23

Answer 1

create table #t
(id  int,
time datetime,
value int)
insert into #t
select 1,'2011-01-01',3  union all      
select 1,'2011-01-02',12 union all      
select 1,'2011-01-03',32 union all      
select 1,'2011-01-04',43 union all      
select 1,'2011-01-04',20 union all      
select 2,'2011-01-01',12 union all      
select 2,'2011-01-01',43 union all      
select 3,'2011-01-01',23                create table #t1
(id  int,
value int)
insert into #t1
select 1,50 union all
select 2,30;with t as (
select *,row_number() over (order by id,time,value) as rid from #t )
select a.id,a.time,a.value from t a left join #t1
on a.id=#t1.id
where (select sum(b.value) from t b where  a.id=b.id and b.rid<=a.rid)>isnull(#t1.value,0)

Answer 2

declare @发货表 table (客户id int,发货时间 datetime,发货金额 int)
insert into @发货表
select 1,'2011-01-01',3 union all
select 1,'2011-01-02',12 union all
select 1,'2011-01-03',32 union all
select 1,'2011-01-04',43 union all
select 1,'2011-01-04',20 union all
select 2,'2011-01-01',12 union all
select 2,'2011-01-01',43 union all
select 3,'2011-01-01',23declare @回款表 table (客户id int,回款金额 int)
insert into @回款表
select 1,50 union all
select 2,30select a.*,isnull(b.回款金额,0) as 回款金额 into #t from @发货表 a 
left join @回款表 b on a.客户id=b.客户idselect 客户id,发货时间=convert(varchar(10),发货时间,120),
发货金额= case row_number() over (partition by 客户id order by rid)
when 1 then 总发货金额-回款金额 else 发货金额 end from (
select *,(select sum(发货金额) from 
(select row_number() over (order by 客户id,发货时间 ) as rid, * from #t) m 
where 客户id=t.客户id and rid<=t.rid) as  总发货金额 from 
(select row_number() over (order by 客户id,发货时间 ) as rid, * from #t) t
)n where 总发货金额-回款金额>0/*
客户id        发货时间       发货金额
----------- ---------- -----------
1           2011-01-04 40
1           2011-01-04 20
2           2011-01-01 25
3           2011-01-01 23
*/

Answer 3

declare @发货表 table (客户id int,发货时间 datetime,发货金额 int)
insert into @发货表
select 1,'2011-01-01',3 union all
select 1,'2011-01-02',12 union all
select 1,'2011-01-03',32 union all
select 1,'2011-01-04',43 union all
select 1,'2011-01-04',20 union all
select 2,'2011-01-01',12 union all
select 2,'2011-01-01',43 union all
select 3,'2011-01-01',23declare @回款表 table (客户id int,回款金额 int)
insert into @回款表
select 1,50 union all
select 2,30select a.*,isnull(b.回款金额,0) as 回款金额 into #t from @发货表 a 
left join @回款表 b on a.客户id=b.客户idselect 客户id,发货时间=convert(varchar(10),发货时间,120),
发货金额= case row_number() over (partition by 客户id order by rid)
when 1 then 总发货金额-回款金额 else 发货金额 end from (
select *,(select sum(发货金额) from 
(select row_number() over (order by 客户id,发货时间 ) as rid, * from #t) m 
where 客户id=t.客户id and rid<=t.rid) as  总发货金额 from 
(select row_number() over (order by 客户id,发货时间 ) as rid, * from #t) t
)n where 总发货金额-回款金额>0/*
客户id        发货时间       发货金额
----------- ---------- -----------
1           2011-01-04 40
1           2011-01-04 20
2           2011-01-01 25
3           2011-01-01 23
*/
--删除临时表。
drop table #t

Answer 4

为什么发货表没有主键呢？ 这样update的时候很不方便的。

Answer 5

create table #t
(id  int,
time datetime,
value int)
insert into #t
select 1,'2011-01-01',3  union all      
select 1,'2011-01-02',12 union all      
select 1,'2011-01-03',32 union all      
select 1,'2011-01-04',43 union all      
select 1,'2011-01-04',20 union all      
select 2,'2011-01-01',12 union all      
select 2,'2011-01-01',43 union all      
select 3,'2011-01-01',23                create table #t1
(id  int,
value int)
insert into #t1
select 1,50 union all
select 2,30;with t as (
select *,row_number() over (order by id,time,value) as rid from #t ),
t1 as(
select a.id,a.time,a.value,
(select sum(b.value) from t b where  a.id=b.id and b.rid<=a.rid) as total,
#t1.value as limit,
row_number()over(partition by a.id order by a.time,a.value) as ranid from t a left join #t1
on a.id=#t1.id
where (select sum(b.value) from t b where  a.id=b.id and b.rid<=a.rid)>isnull(#t1.value,0))
select id,time,
case ranid when 1 then total-isnull(limit,0)
else value end  from t1
id          time                    
----------- ----------------------- -----------
1           2011-01-04 00:00:00.000 17
1           2011-01-04 00:00:00.000 43
2           2011-01-01 00:00:00.000 25
3           2011-01-01 00:00:00.000 23(4 row(s) affected)有个问题 同一天同一个客户会有多比出货，我这里是按照出货金额从小到大排序的，这样可以保证当同一天有多比出货金额的情况下，小的出货金额可以优先被处理掉。

Answer 6

如果是结果集，用row_number()加上序号，然后有唯一标识了，然后可以嵌套。
问题是如果要更新都是更新表的，弄个结果集怎么更新？

Answer 7

select row_number() over(partition by id order by getdate()) no,* into #tb from tb1
select a.id,a.senddate,sendtime=(case when ((select sum(SENDMONEY) from #tb where id=a.id 
                  and no<=a.no)-isnull(b.RECEIVEMONEY,0))< a.SENDMONEY
              then ((select sum(SENDMONEY) from #tb where id=a.id 
                  and no<=a.no)-isnull(b.RECEIVEMONEY,0))
                   else a.SENDMONEY end)
from #tb a left join tb2 b on a.id=b.id 
  where (select sum(SENDMONEY) from #tb where id=a.id 
                  and no<=a.no)>=isnull(b.RECEIVEMONEY,0)
drop table #tb/*
id          senddate                sendtime
----------- ----------------------- ---------------------------------------
1           2011-01-04 00:00:00.000 40.00
1           2011-01-04 00:00:00.000 20.00
2           2011-01-01 00:00:00.000 25.00
3           2011-01-01 00:00:00.000 23.00