一个超复杂的sql实现的逻辑

[chentao01]

表一 余额表 | 表二 明细表
企业编码 入账时间 余额 | 企业编码 借款时间 借款余额
001 2007.01.01 40 | 001 2005.01.01 10
002 2007.02.01 100 | 001 2005.03.01 10
| 001 2005.09.01 5
| 001 2006.01.01 5
| 001 2007.01.01 5
| 002 2005.01.01 120
我想要得到的结果:

企业编码 借款时限 欠款余额
001 0 0
001 1 0
001 2 5
002 2 20
关系
借款时限 = 入帐时间(年)- 借款时间(年)
欠款余额 = 余额 - 借款余额年度累加植
如001企业:
借款时限: 2007-2007 = 0 年 借款金额:40 - 5 =35 >= 0 所以 欠款金额 = 0
借款时限: 2007-2006 = 1 年 借款金额:40 - 5-5 =30 >= 0 所以 欠款金额 = 0
借款时限: 2007-2005 = 2 年 借款金额:40-5-5-10-10-5 =-5 所以 欠款金额 = 0
最终得到: 企业编码 借款时限 欠款余额
001 0 0
001 1 0
001 2 5

[天-笑]

declare @in	table
(enterprise varchar(8)	not null,
 date		datetime	not null,
 money		float		null)
insert into @in
select '001','2007-01-01',40
union all
select '002','2007-02-01',100

declare @out	table
(enterprise varchar(8)	not null,
 date		datetime	not null,
 money		float		null)
insert into @out
select '001','2005-03-01',10
union all
select '001','2005-09-01',5
union all
select '001','2006-01-01',5
union all
select '001','2007-01-01',5
union all
select '002','2005-01-01',123

select a.enterprise ,a.date - b.date ,in_money - out_money as money from 
(	select enterprise,max(year(date)) as date ,sum(money) as in_money from @in
	group by enterprise 
) a left join 
(
	select a.enterprise,a.date,sum(b.money) as out_money from 
	(select distinct enterprise enterprise,year(date) date from @out) a 
	left join @out b on year(b.date)<=a.date and a.enterprise = b.enterprise
	group by a.enterprise,a.date
) b  on a.enterprise = b.enterprise 

[tianhuo_soft]

好复杂

[ws_hgo]

没有看懂

[天-笑]

declare @in	table
(enterprise varchar(8)	not null,
 date		datetime	not null,
 money		float		null)
insert into @in
select '001','2007-01-01',40
union all
select '002','2007-02-01',100

declare @out	table
(enterprise varchar(8)	not null,
 date		datetime	not null,
 money		float		null)
insert into @out
select '001','2005-03-01',10
union all
select '001','2005-09-01',5
union all
select '001','2006-01-01',5
union all
select '001','2007-01-01',5
union all
select '002','2005-01-01',123


select a.enterprise ,a.date,b.date,in_money,out_money,a.date - b.date as  [借款时限],in_money - out_money as money from 
(	select enterprise,max(year(date)) as date ,sum(money) as in_money from @in
	group by enterprise 
) a left join 
(
	select a.enterprise,a.date,sum(b.money) as out_money from 
	(select distinct enterprise enterprise,year(date) date from @out) a 
	left join @out b on year(b.date)>=a.date and a.enterprise = b.enterprise
	group by a.enterprise,a.date
) b  on a.enterprise = b.enterprise  


(2 行受影响)

(5 行受影响)
enterprise date        date        in_money               out_money              借款时限        money
---------- ----------- ----------- ---------------------- ---------------------- ----------- ----------------------
001        2007        2005        40                     25                     2           15
001        2007        2006        40                     10                     1           30
001        2007        2007        40                     5                      0           35
002        2007        2005        100                    123                    2           -23

(4 行受影响)