SQL语句，有一定难度

求一个SQL语句，有一定难度
该表目的是统计配件的库龄，能直接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 @a-@b,@b-@c,@c-@d,@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 
 
[解决办法]

with t as (
select NAME
from A001 a  
where not exists (select 1 from A001 b where a.NAME=b.NAME and b.operate_date>a.operate_date )
),t9 as(
select  
NAME
,(select a.stock_num from A001 a where a.NAME=b.NAME and DATEDIFF(dd,a.operate_date,GETDATE())=9) 第9天前库存
,(select abs(SUM(case when in_out_num <0 then in_out_num else 0 end)) from A001 a where a.NAME=b.NAME and DATEDIFF(dd,a.operate_date,GETDATE())<9) 第9天内出
from t b
),t99 as (
select  
NAME
,case when 第9天前库存<第9天内出 then 0 else 第9天前库存-第9天内出 end [9天以上]
from t9
), t7 as (
select  
*
,(select abs(SUM(case when in_out_num >0 then in_out_num else 0 end))  from A001 a where a.NAME=b.NAME and (DATEDIFF(dd,a.operate_date,GETDATE())<9 and DATEDIFF(dd,a.operate_date,GETDATE())>=7)) 第78内进
,(select abs(SUM(case when in_out_num <0 then in_out_num else 0 end))  from A001 a where a.NAME=b.NAME and (DATEDIFF(dd,a.operate_date,GETDATE())<9 and DATEDIFF(dd,a.operate_date,GETDATE())>=7)) 第78内出
from t99 b
),t77 as (
select  
*
,第78内进-case when [9天以上]-第78内出>=0 then 0 else [9天以上]-第78内出 end [7-8天]
from t7  
)
,t5 as (
select  
*
,(select abs(SUM(case when in_out_num >0 then in_out_num else 0 end))  from A001 a where a.NAME=b.NAME and (DATEDIFF(dd,a.operate_date,GETDATE())<7 and DATEDIFF(dd,a.operate_date,GETDATE())>=5)) 第56内进
,(select abs(SUM(case when in_out_num <0 then in_out_num else 0 end))  from A001 a where a.NAME=b.NAME and (DATEDIFF(dd,a.operate_date,GETDATE())<7 and DATEDIFF(dd,a.operate_date,GETDATE())>=5)) 第56内出
from t77 b
)
,t55 as (
select  
*
,第56内进-case when [9天以上]+[7-8天]-第78内出-第56内出>=0 then 0 else [9天以上]+[7-8天]-第78内出-第56内出 end [5-6天]
from t5  
)
,t3 as (
select  
*
,(select abs(SUM(case when in_out_num >0 then in_out_num else 0 end))  from A001 a where a.NAME=b.NAME and (DATEDIFF(dd,a.operate_date,GETDATE())<5 and DATEDIFF(dd,a.operate_date,GETDATE())>=3)) 第34内进
,(select abs(SUM(case when in_out_num <0 then in_out_num else 0 end))  from A001 a where a.NAME=b.NAME and (DATEDIFF(dd,a.operate_date,GETDATE())<5 and DATEDIFF(dd,a.operate_date,GETDATE())>=3)) 第34内出
from t55 b
)
,t33 as (
select  
*,第34内进-case when [9天以上]+[7-8天]+[5-6天]-第78内出-第56内出-第34内出>=0 then 0 else [9天以上]+[7-8天]+[5-6天]-第78内出-第56内出-第34内出 end [3-4天]
from t3  
)
,t0 as (
select  
*
,(select abs(SUM(case when in_out_num >0 then in_out_num else 0 end))  from A001 a where a.NAME=b.NAME and (DATEDIFF(dd,a.operate_date,GETDATE())<3)) 第012内进
,(select abs(SUM(case when in_out_num <0 then in_out_num else 0 end))  from A001 a where a.NAME=b.NAME and (DATEDIFF(dd,a.operate_date,GETDATE())<3)) 第012内出
from t33 b
)
,t00 as (
select  
*,第012内进-case when [9天以上]+[7-8天]+[5-6天]+[3-4天]-第78内出-第56内出-第34内出-第012内出>=0 then 0 else [9天以上]+[7-8天]+[5-6天]+[3-4天]-第78内出-第56内出-第34内出-第012内出 end [2天内]
from t0  
)
select  
NAME,[2天内],[3-4天],[5-6天],[7-8天],[9天以上]
from t00 

 

查看更多 下一篇