create table table1(A datetime,B varchar(10),C int)
insert into table1 select '2011-11-2','001',60
insert into table1 select '2011-11-7','001',90
insert into table1 select '2011-11-8','001',100
insert into table1 select '2011-11-6','002',40
insert into table1 select '2011-11-7','002',60
create table table2(B varchar(10),C int)
insert into table2 select '001',120
insert into table2 select '002',80
go
;with c1 as(
select row_number()over(partition by B order by A desc)r,* from table1
),c2 as(
select a.r,a.A,a.B,(case when a.c<b.c then a.c else b.c end)C,(case when a.c<b.c then b.c-a.c else 0 end)D
from c1 a inner join table2 b on a.B=b.B and a.r=1
union all
select a.r,a.A,a.B,(case when a.c<b.d then a.c else b.d end)C,(case when a.c<b.d then b.d-a.c else 0 end)D
from c1 a inner join c2 b on a.B=b.B and a.r=b.r+1 and b.D>0
)select A,B,C from c2 order by B,A
/*
A B C
----------------------- ---------- -----------
2011-11-07 00:00:00.000 001 20
2011-11-08 00:00:00.000 001 100
2011-11-06 00:00:00.000 002 20
2011-11-07 00:00:00.000 002 60
(4 行受影响)
*/
go
drop table table1,table2
