库存出入库明细表，SQL查询

Question

期初数据（期初日期，产品代号，期初数量）
2008-1-1，10001，100 入库表（入库日期，产品代号，入库数量）
2008-1-2，10001，300
2008-2-2，10001，400出库表（出库日期，产品代号，出库数量）
2008-1-2，10001，200
2008-1-3，10001，100
希望结果如下：
   日期   产品代号 期初数量  入库数量 出库数量  结余
2008-1-1    10001   100      0       0      100
2008-1-2    10001   100      300     200    200
2008-1-3    10001   200      0       100    100
2008-2-2    10001   100      400     0    500

Answer 1

---测试数据---
if object_id('[期初数据]') is not null drop table [期初数据]
go
create table [期初数据]([期初日期] datetime,[产品代号] int,[期初数量] int)
insert [期初数据]
select '2008-1-1',10001,100
if object_id('[入库表]') is not null drop table [入库表]
go
create table [入库表]([入库日期] datetime,[产品代号] int,[入库数量] int)
insert [入库表]
select '2008-1-2',10001,300 union all
select '2008-2-2',10001,400
if object_id('[出库表]') is not null drop table [出库表]
go
create table [出库表]([出库日期] datetime,[产品代号] int,[出库数量] int)
insert [出库表]
select '2008-1-2',10001,200 union all
select '2008-1-3',10001,100
 
---查询---
select *
into #
from
(
select 期初日期 as 日期,产品代号,期初数量,0 as 入库数量,0 as 出库数量,期初数量 as 结余 from 期初数据
union all
select 入库日期,产品代号,0,入库数量,0,0 from 入库表
union all
select 出库日期,产品代号,0,0,出库数量,0 from 出库表
) t
goselect
convert(varchar(10),日期,120) as 日期,
产品代号,
期初数量=期初数量
       +isnull((select sum(入库数量-出库数量+结余) from # where 产品代号=t.产品代号 and 日期<t.日期),0),
sum(入库数量) as 入库数量,
sum(出库数量) as 出库数量,
结余=isnull((select sum(入库数量-出库数量+结余) from # where 产品代号=t.产品代号 and 日期<=t.日期),0)
from # t
group by 日期,产品代号,期初数量---结果---
日期         产品代号        期初数量        入库数量        出库数量        结余          
---------- ----------- ----------- ----------- ----------- ----------- 
2008-01-01 10001       100         0           0           100
2008-01-02 10001       100         300         200         200
2008-01-03 10001       200         0           100         100
2008-02-02 10001       100         400         0           500（所影响的行数为 4 行）drop table #

Answer 2

if object_id('[期初数据]') is not null drop table [期初数据]
go
create table [期初数据]([期初日期] datetime,[产品代号] int,[期初数量] int)
insert [期初数据]
select '2008-1-1',10001,100
if object_id('[入库表]') is not null drop table [入库表]
go
create table [入库表]([入库日期] datetime,[产品代号] int,[入库数量] int)
insert [入库表]
select '2008-1-2',10001,300 union all
select '2008-2-2',10001,400
if object_id('[出库表]') is not null drop table [出库表]
go
create table [出库表]([出库日期] datetime,[产品代号] int,[出库数量] int)
insert [出库表]
select '2008-1-2',10001,200 union all
select '2008-1-3',10001,100
go
--语句
with cte as
(
select 日期=日期,产品代号,期初数量=MAX(期初数量),入库数量=MAX(入库数量),出库数量=MAX(出库数量)
from (
select  [期初日期] as 日期,产品代号,期初数量,入库数量=0,出库数量=0 from [期初数据]
union 
select [入库日期] as 日期,产品代号,期初数量=0,入库数量,出库数量=0 from [入库表]
union 
select [出库日期] as 日期,产品代号,期初数量=0,入库数量=0,出库数量 from [出库表])k 
group by 日期,产品代号)
select 日期=CONVERT(varchar(10),日期,120),产品代号,
期初数量=case when 期初数量=0 then (select SUM(入库数量-出库数量+期初数量) from cte where 日期<a.日期)end,
入库数量,出库数量,
结余=(select SUM(入库数量-出库数量+期初数量) from cte where 日期<=a.日期)
from cte a 
/*(1 行受影响)(2 行受影响)(2 行受影响)
日期         产品代号        期初数量        入库数量        出库数量        结余
---------- ----------- ----------- ----------- ----------- -----------
2008-01-01 10001       NULL        0           0           100
2008-01-02 10001       100         300         200         200
2008-01-03 10001       200         0           100         100
2008-02-02 10001       100         400         0           500(4 行受影响)*/

Answer 3

CREATE TABLE 期初数据
(
期初日期 DATETIME
,产品代号 INT
,期初数量 INT 
)
CREATE TABLE 入库表
(
入库日期 DATETIME
,产品代号 INT 
,入库数量 INT 
) 
CREATE TABLE 出库表
(
出库日期 DATETIME
,产品代号 INT 
,出库数量 INT 
)
INSERT INTO 期初数据
SELECT '2008-1-1',10001,100
INSERT INTO 入库表
SELECT '2008-1-2',10001,300 
union all select '2008-2-2',10001,400INSERT INTO  出库表
SELECT '2008-1-2',10001,200 
UNION ALL SELECT '2008-1-3',10001,100    
WITH cte
AS 
(
SELECT  期初日期,产品代号, 期初数量 AS 期初数量, 0 AS 入库数量, 0 AS 出库数量, 期初数量 AS 结余
FROM 期初数据
UNION ALL 
SELECT 入库日期,产品代号, 0, 入库数量, 0, 0
FROM 入库表
UNION ALL 
SELECT 出库日期,产品代号, 0,  0 ,出库数量, 0
FROM 出库表 
)
SELECT CONVERT(CHAR(10),期初日期,120) AS 期初日期
       ,产品代号
       ,期初数量 = 期初数量 + ISNULL((SELECT SUM(入库数量 - 出库数量 + 结余 ) FROM cte WHERE 产品代号 = c.产品代号 AND 期初日期 < c.期初日期),0)
       ,入库数量 = SUM(入库数量) 
       ,出库数量 = SUM(出库数量)
       ,结余 = ISNULL((SELECT SUM(入库数量 - 出库数量 + 结余 ) FROM cte WHERE 产品代号 = c.产品代号 AND 期初日期 <= c.期初日期),0)
FROM cte c
GROUP BY 期初日期,产品代号,期初数量
----------
期初日期       产品代号        期初数量        入库数量        出库数量        结余
---------- ----------- ----------- ----------- ----------- -----------
2008-01-01 10001       100         0           0           100
2008-01-02 10001       100         300         200         200
2008-01-03 10001       200         0           100         100
2008-02-02 10001       100         400         0           500