该表目的是统计配件的库龄，能直接SQL最好，实在不行的存储过程也可以
测试数据如下
- SQL code
drop table a001;create table A001(NAME varchar(10),operate_date date ,in_out_num int,stock_num int)insert into A001 values('001',date('2012-09-01'),1,9);insert into A001 values('001',date('2012-09-02'),-1,8);insert into A001 values('001',date('2012-09-03'),1,9);insert into A001 values('001',date('2012-09-04'),-3,6);insert into A001 values('001',date('2012-09-05'),-1,5);insert into A001 values('001',date('2012-09-06'),2,7);insert into A001 values('001',date('2012-09-07'),-1,6);根据先进先出原则，9月1号到现在还剩下配件9-1-3-1-1 = 3个
其他的 3号6号的配件没有使用，则3号到现在剩余的有1个
6号有2个
假设当前日期是2012-09-10，则最终统计的库龄结果如下
- SQL code
name 3-4天 5-6天 7-8天 9天以上001 2 0 1 3
------解决方案--------------------
下面这方法必须数据都对，所以加了一条 入库的数据(真实数据应该是有类似入库的数据)
直接用一条语句也可以直接查出来，直接连起来就可以，需要的name条件自己加，你也可以改成存储过程或函数
- SQL code
--insert a001 values('001','2012-08-31',8,8);declare @a int,@b int,@c int,@d int;declare @date date='2012-09-10'; set @d=(select SUM(in_out_num) from a001where DATEDIFF(day,operate_date,@date)>=9 and in_out_num>0)+(select SUM(in_out_num) from a001where in_out_num<0) set @C=(select SUM(in_out_num) from a001where DATEDIFF(day,operate_date,@date)>=7 and in_out_num>0)+(select SUM(in_out_num) from a001where in_out_num<0) set @B=(select SUM(in_out_num) from a001where DATEDIFF(day,operate_date,@date)>=5 and in_out_num>0)+(select SUM(in_out_num) from a001where in_out_num<0) set @A=(select SUM(in_out_num) from a001where DATEDIFF(day,operate_date,@date)>=3 and in_out_num>0)+(select SUM(in_out_num) from a001where in_out_num<0)select @[email protected],@[email protected],@[email protected],@d
------解决方案--------------------
SQL2005的写法如下，SQL2000的代码要不2005繁琐，就不写了- SQL code
with cte_tas(select Name,operate_date,A.in_out_num+B.in_out_num in_out_num from( select Name,operate_date,in_out_num+isnull(sum(in_out_numB),0) as in_out_num from( select A.*,B.operate_date operate_dateB,B.in_out_num in_out_numB from (select A.Name,A.operate_date,isnull(B.stock_num,A.in_out_num) in_out_num from A001 A left join(select * from A001 where operate_date=(select min(operate_date) from A001)) B ON A.operate_date=B.operate_date where A.in_out_num>0 ) as A left join (select A.Name,A.operate_date,isnull(B.stock_num,A.in_out_num) in_out_num from A001 A left join(select * from A001 where operate_date=(select min(operate_date) from A001)) B ON A.operate_date=B.operate_date where A.in_out_num>0 ) as B on A.operate_date>B.operate_date ) A group by Name,operate_date,in_out_num) A left join(select sum(in_out_num) in_out_num from A001 where in_out_num<0) B on 1=1)select NAME, [3-4天] as [3-4天], [5-6天] as [5-6天], [7-8天] as [7-8天], [9天以上] as [9天以上] from(select isnull(A.NAME,'001') NAME,isnull(A.in_out_num,0) in_out_num,B.day from(select Name,DATEDIFF(day,operate_date,'2012-9-10') day,in_out_num from( select A.Name,isnull(B.operate_date,A.operate_date) operate_date,A.in_out_num+isnull(C.in_out_num,0) in_out_num from (select A.Name,A.operate_date,isnull(B.stock_num,A.in_out_num) in_out_num from A001 A left join(select * from A001 where operate_date=(select min(operate_date) from A001)) B ON A.operate_date=B.operate_date where A.in_out_num>0 ) A left join(select * from cte_t where in_out_num=(select min(in_out_num) from cte_t where in_out_num>0)) B on A.operate_date<=B.operate_date left join(select sum(in_out_num) in_out_num from A001 where in_out_num<0) C on A.operate_date=((select min(operate_date) from cte_t where in_out_num>0)) where A.in_out_num>0) A ) Aright join(select 3 minday,4 maxday,'3-4天' day union all select 5 minday,6 maxday,'5-6天' day union all select 7 minday,8 maxday,'7-8天' day union all select 9 minday,100000 maxday,'9天以上' day) B on A.day>=B.minday and A.day<=B.maxday) Apivot(sum(in_out_num)for day in([3-4天],[5-6天],[7-8天],[9天以上]))as pvt