有一个表结构为
帐号 起算日期 变动日期 余额
001 20070205 20070506 1000.00
001 20070205 20070806 1200.00
001 20070205 20071106 1400.00
001 20070205 29999999 1400.00
002 20070109 20070710 500.00
002 20070109 29999999 500.00
要求计算每个帐号在2007/8月的每天平均余额
例如001的余额就是（1000*5+1200*26）/31
002就是500*31/31=500.00
------解决方案--------------------
- SQL code
--原始数据：@Tdeclare @T table(帐号 varchar(3),起算日期 varchar(8),变动日期 varchar(8),余额 decimal(6,2))insert @Tselect '001',20070205,20070506,1000.00 union allselect '001',20070205,20070806,1200.00 union allselect '001',20070205,20071106,1400.00 union allselect '001',20070205,29999999,1400.00 union allselect '002',20070109,20070710,500.00 union allselect '002',20070109,29999999,500.00declare @1 varchar(8),@2 varchar(8)select @1 = '20070801',@2='20070901'select 帐号=coalesce(a.帐号,b.帐号),余额=(isnull(a.余额,b.余额)*datediff(day,isnull(a.变动日期,@1),isnull(b.变动日期,@2))+isnull(b.余额,0)*datediff(day,isnull(b.变动日期,@1),@2))/datediff(day,@1,@2)from(select 帐号,[email protected],余额 from @T a where 变动日期=(select max(变动日期) from @T where 帐号=a.帐号 and 变动日期<@1)) afull join(select 帐号,变动日期,余额 from @T where left(变动日期,6)=left(@1,6)) bon a.帐号=b.帐号/*帐号 余额001 1167.7419354838709002 500.0000000000000*/