在论坛中出现的比较难的sql问题：40(子查询 销售和历史库存)

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

求教：我有个表有入库时间，有出库时间，我想得到该样式号每月的销售和历史库存

http://bbs.csdn.net/topics/390629790

库结构大概是这样：
货号    样式号    入库时间    出库时间
a001    10    2011-1-10        
b002    10    2011-1-10    2011-2-1
c003    10    2012-1-15    2012-2-2
d004    10    2013-2-3    2013-1-5
e005    10    2013-2-3        
f006    15    2011-2-15    2011-3-16
g007    15    2011-2-16    2012-3-16
h009    15    2013-1-10    
m012    18    2011-1-4        
c009    18    2011-4-5    2012-5-6
f008    18    2012-2-19    2013-1-1
e008    18    2013-1-5    2013-2-6
 
我想得到的结果：
样式号    日期    销售件数    库存件数    
10    2011-2    1    1
10    2012-2    1    1
10    2013-1    1    2
15    2011-3    1    0
15    2012-3    1    1
18    2012-5    1    1
18    2013-1    1    1
18    2013-2    1    1

我的解法：

if object_id(‘tb‘) is not null
   drop table tb
go
 
create table tb 
(
[货号] varchar(20),[样式号] int,
[入库时间] datetime,
[出库时间] datetime
) 
 
insert into tb
SELECT ‘a001‘,10,‘2011-01-10‘,null UNION ALL
SELECT ‘b002‘,10,‘2011-01-10‘,‘2011-02-01‘ UNION ALL
SELECT ‘c003‘,10,‘2012-01-15‘,‘2012-02-02‘ UNION ALL
SELECT ‘d004‘,10,‘2013-01-03‘,‘2013-01-05‘ UNION ALL
SELECT ‘e005‘,10,‘2013-01-03‘,null UNION ALL
SELECT ‘f006‘,15,‘2011-02-15‘,‘2011-03-16‘ UNION ALL
SELECT ‘g007‘,15,‘2011-02-16‘,‘2012-03-16‘ UNION ALL
SELECT ‘h009‘,15,‘2013-01-10‘,null UNION ALL
SELECT ‘m012‘,18,‘2011-01-04‘,null UNION ALL
SELECT ‘c009‘,18,‘2011-04-05‘,‘2012-05-06‘ UNION ALL
SELECT ‘f008‘,18,‘2012-02-19‘,‘2013-01-01‘ UNION ALL
SELECT ‘e008‘,18,‘2013-01-05‘,‘2013-02-06‘
go
 
 
;with t
as(
select *,
       row_number() over(partition by 样式号 
                             order by 入库时间,出库时间) as rownum
from tb
),
 
tt
as
(
select *,
       case when 出库时间 is null 
                 then (select top 1 出库时间 
                       from t t2 
                       where t1.样式号 = t2.样式号 and
                             t1.rownum > t2.rownum
                       order by t2.rownum desc)
            else 出库时间
       end as prior_row,
       
       case when 出库时间 is null 
                 then (select top 1 出库时间 
                       from t t2 
                       where t1.样式号 = t2.样式号 and
                             t1.rownum < t2.rownum
                       order by t2.rownum )
            else 出库时间
       end as next_row
       
from t t1
),
 
ttt
as
(
select 样式号,
       convert(varchar(7),isnull(next_row,prior_row),120) as 日期,
       count(出库时间) 销售件数,
       count(入库时间) 库存件数,
       count(入库时间) - count(出库时间) 剩余库存
       --row_number() over(partition by 样式号 
       --order by convert(varchar(7),isnull(next_row,prior_row),120)) as rownum
from tt
group by 样式号,
         convert(varchar(7),isnull(next_row,prior_row),120)
 
)
 
select t1.样式号,t1.日期,
       isnull(t1.销售件数,0) as 销售件数 ,
       
       isnull(t1.库存件数,0) + 
       isnull((select  sum(库存件数)-sum(销售件数) as 剩余库存 from ttt t2 
               where t2.样式号 = t1.样式号
                     and t2.日期 < t1.日期
               ),0) -
       isnull(t1.销售件数,0) as 库存件数
       
from ttt t1
order by t1.样式号 
/*
样式号	日期	    销售件数	库存件数
10	    2011-02	1	    1
10	    2012-02	1	    1
10	    2013-01	1	    2
15	    2011-03	1	    0
15	    2012-03	1	    1
18	    2012-05	1	    1
18	    2013-01	1	    1
18	    2013-02	1	    1
*/