--写出来很复杂,感觉不如写个过程,用变量来写。 --如果你是mysql或者是SQL2000...没有 with 关键字 的话 ,劝你还是看懂后写过程吧... 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
拿分--测试数据 create table A001(NAME varchar(10),operate_date date ,in_out_num int,stock_num int) insert into A001 values('001', '2012-09-01',1,9); insert into A001 values('001', '2012-09-02',-1,8); insert into A001 values('001', '2012-09-03',1,9); insert into A001 values('001', '2012-09-04',-3,6); insert into A001 values('001', '2012-09-05',-1,5); insert into A001 values('001', '2012-09-06',2,7); insert into A001 values('001', '2012-09-07',-1,6);--解决方案 ;WITH CET1 AS(select a.*,b.*,CASE WHEN a.stock_num + b.sumout > 0 THEN a.stock_num + b.sumout ELSE 0 END renum from A001 a outer apply (select isnull(sum(in_out_num),0)sumout --解题关键在这 from A001 b where a.name = b.name and b.operate_date > a.operate_date and in_out_num < 0) b where datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) <=9 )--注意,只取9天内的统计记录 ,CET2 as (SELECT NAME,operate_date,renum,DN FROM(SELECT NAME,operate_date,renum, ROW_NUMBER()OVER(PARTITION BY NAME,RENUM ORDER BY operate_date) RN, dense_rank()over(partition by NAME ORDER BY RENUM)DN FROM CET1)A WHERE rn = 1 ), CET3 AS(select a.name,a.renum - isnull(b.renum,0) as num, case when datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) between 3 and 4 then '3-4天' when datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) between 5 and 6 then '5-6天' when datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) between 7 and 8 then '7-8天' when datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) > 8 then '9天以上' end as GroupName from CET2 a left join CET2 b on a.name = b.name and a.dn = b.dn+1 )select name,isnull([3-4天],0)[3-4天],isnull([5-6天],0)[5-6天],isnull([7-8天],0)[7-8天],isnull([9天以上],0)[9天以上] from CET3 pivot(sum(num) for GroupName in([3-4天],[5-6天],[7-8天],[9天以上]))p /* name 3-4天 5-6天 7-8天 9天以上 ---------- ----------- ----------- ----------- ----------- 001 2 0 1 3(1 行受影响) */
--如果你是mysql或者是SQL2000...没有 with 关键字 的话 ,劝你还是看懂后写过程吧...
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
create table A001(NAME varchar(10),operate_date date ,in_out_num int,stock_num int)
insert into A001 values('001', '2012-09-01',1,9);
insert into A001 values('001', '2012-09-02',-1,8);
insert into A001 values('001', '2012-09-03',1,9);
insert into A001 values('001', '2012-09-04',-3,6);
insert into A001 values('001', '2012-09-05',-1,5);
insert into A001 values('001', '2012-09-06',2,7);
insert into A001 values('001', '2012-09-07',-1,6);--解决方案
;WITH CET1 AS(select a.*,b.*,CASE WHEN a.stock_num + b.sumout > 0 THEN a.stock_num + b.sumout ELSE 0 END renum
from A001 a
outer apply (select isnull(sum(in_out_num),0)sumout --解题关键在这
from A001 b
where a.name = b.name and b.operate_date > a.operate_date and in_out_num < 0) b
where datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) <=9 )--注意,只取9天内的统计记录
,CET2 as (SELECT NAME,operate_date,renum,DN
FROM(SELECT NAME,operate_date,renum,
ROW_NUMBER()OVER(PARTITION BY NAME,RENUM ORDER BY operate_date) RN,
dense_rank()over(partition by NAME ORDER BY RENUM)DN
FROM CET1)A
WHERE rn = 1
),
CET3 AS(select a.name,a.renum - isnull(b.renum,0) as num,
case when datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) between 3 and 4 then '3-4天'
when datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) between 5 and 6 then '5-6天'
when datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) between 7 and 8 then '7-8天'
when datediff(dd,a.operate_date,'2012-09-10'/*这里是假设的日期*/) > 8 then '9天以上' end as GroupName
from CET2 a left join CET2 b on a.name = b.name and a.dn = b.dn+1
)select name,isnull([3-4天],0)[3-4天],isnull([5-6天],0)[5-6天],isnull([7-8天],0)[7-8天],isnull([9天以上],0)[9天以上]
from CET3 pivot(sum(num) for GroupName in([3-4天],[5-6天],[7-8天],[9天以上]))p
/*
name 3-4天 5-6天 7-8天 9天以上
---------- ----------- ----------- ----------- -----------
001 2 0 1 3(1 行受影响)
*/