实现仓库结存的SQL问题 !

Question

这是SQL测试语句:
--结存表
CREATE TABLE Stocks(序号 int IDENTITY PRIMARY KEY,物料编码 varchar(10),供应商编号 varchar(10),结存年月 int,结存量 int)
INSERT Stocks SELECT 'aa','VT001',NULL,0
INSERT Stocks SELECT 'aa','VH010',NULL,0
UNION  ALL    SELECT 'bb','VT002',NULL,0
UNION  ALL    SELECT 'cc','VT003',NULL,0--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
物料编码 varchar(10),  --产品编号
供应商编号 varchar(10),--供应商编码
数量 int,      --交易数量
类型 int,          --交易标志,0代表入库,1代表出库,2代表退料,3代表退货.
日期 datetime)     --交易日期
INSERT tb SELECT 'aa','VT001',100,0,'2005-1-1'
UNION ALL SELECT 'aa','VT001',90 ,0,'2005-2-1'
UNION ALL SELECT 'aa','VT001',55 ,1,'2005-2-1'
UNION ALL SELECT 'aa','VT001',10,2,'2005-2-2'
UNION ALL SELECT 'aa','VT001',5 ,3,'2005-2-3'
UNION ALL SELECT 'aa','VT001',200,0,'2005-2-2'
UNION ALL SELECT 'aa','VT001',90 ,0,'2005-2-1'UNION ALL SELECT 'aa','VH010',150 ,0,'2005-2-1'
UNION ALL SELECT 'aa','VH010',15 ,1,'2005-2-1'
UNION ALL SELECT 'aa','VH010',25,2,'2005-2-2'
UNION ALL SELECT 'aa','VH010',10 ,3,'2005-2-3'
UNION ALL SELECT 'aa','VH010',54,0,'2005-2-2'
UNION ALL SELECT 'aa','VH010',12 ,0,'2005-3-1'UNION ALL SELECT 'bb','VT002',95 ,0,'2005-2-2'
UNION ALL SELECT 'bb','VT002',65 ,1,'2005-2-3'
UNION ALL SELECT 'bb','VT002',15,2,'2005-2-5'
UNION ALL SELECT 'bb','VT002',20,3,'2005-2-5'
UNION ALL SELECT 'bb','VT002',100,0,'2005-2-7'
UNION ALL SELECT 'bb','VT002',50,1,'2005-3-7'
UNION ALL SELECT 'cc','VT003',100,0,'2005-2-7'
GO--查询时间段定义
DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate='2005-2-1',@EndDate='2005-4-1'--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @Proiod int,@dt datetime
SELECT @Proiod=CONVERT(CHAR(6),DATEADD(Month,-1,@StartDate),112),
    @dt=DATEADD(Day,1-Day(@StartDate),@StartDate)--查询期初库存
SELECT
物料编码=ISNULL(a.物料编码,b.物料编码),
供应商编号=ISNULL(a.供应商编号,b.供应商编号),
    日期=ISNULL(b.日期,CONVERT(char(10),@StartDate,120)),
    初存量=ISNULL(a.结存量,0)+ISNULL(b.初存量,0),
    入库=ISNULL(b.入库,0),
    退货=ISNULL(b.退货,0),
    发料=ISNULL(b.发料,0),
    退料=ISNULL(b.退料,0),
    结存量=ISNULL(a.结存量,0)+ISNULL(b.初存量,0)+ISNULL(b.末存量,0)
FROM(
    --期初数
    SELECT 物料编码,供应商编号,结存量 FROM Stocks WHERE 结存年月=@Proiod
)a FULL JOIN(
    --统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)
    SELECT 
物料编码,
        供应商编号,
日期=CONVERT(char(10),@StartDate,120),    
        初存量=SUM(CASE WHEN (类型=0 OR 类型=2) THEN 数量 ELSE -数量 END),
        入库=0,
        退货=0,
        发料=0,
        退料=0,
        末存量=0
    FROM tb a
    WHERE 日期>=@dt AND 日期<@StartDate 
        AND NOT EXISTS(
            SELECT * FROM tb WHERE 物料编码=a.物料编码 AND 供应商编号=a.供应商编号 AND 日期>@StartDate AND 日期<DATEADD(Day,1,@EndDate))
    GROUP BY 物料编码,供应商编号
    UNION ALL
    --指定时间段内有交易发生的数据
    SELECT 
物料编码,
供应商编号,
        日期=CONVERT(char(10),日期,120),    
        初存量=(SELECT SUM(CASE WHEN 类型=0 OR 类型=2  THEN 数量 ELSE -数量 END)
            FROM tb WHERE 物料编码=a.物料编码 AND 日期>=@dt AND 日期<MIN(a.日期)),
        入库=SUM(CASE WHEN 类型=0 THEN 数量 END),
        退货=SUM(CASE WHEN 类型=3  THEN 数量 END),
        发料=SUM(CASE WHEN 类型=1 THEN 数量 END),
        退料=SUM(CASE WHEN 类型=2 THEN 数量 END),
        末存量=SUM(CASE WHEN 类型=0 OR 类型=2 THEN 数量 ELSE -数量 END)
    FROM tb a
    WHERE 日期>=@StartDate AND 日期<DATEADD(Day,1,@EndDate)
    GROUP BY CONVERT(char(10),日期,120),物料编码,供应商编号
)b ON a.物料编码=b.物料编码 And a.供应商编号=b.供应商编号
ORDER BY 物料编码,供应商编号,日期GOIF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'Stocks')
   DROP Table Stocks
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tb')
   DROP Table tb
GO

Answer 1

问题在'aa'有两个供应商,如果将下面的测试数据注释掉,统计是正确的:--UNION ALL SELECT 'aa','VH010',150 ,0,'2005-2-1'
--UNION ALL SELECT 'aa','VH010',15 ,1,'2005-2-1'
--UNION ALL SELECT 'aa','VH010',25,2,'2005-2-2'
--UNION ALL SELECT 'aa','VH010',10 ,3,'2005-2-3'
--UNION ALL SELECT 'aa','VH010',54,0,'2005-2-2'
--UNION ALL SELECT 'aa','VH010',12 ,0,'2005-3-1'

Answer 2

正确的结果应该是:物料编码     供应商编号   日期               初存量         入库          退货          发料          退料         结存量
--------   ---------  ----------       ----------   ----------  ----------- ----------- ----------  ---------
aa         VT001      2005-02-01         0           180         0           55          0           125
aa         VT001      2005-02-02         125         200         0           0           10          335
aa         VT001      2005-02-03         335         0           5           0           0           330
aa         VH010      2005-02-01         0           150         0           15          0           135
aa         VH010      2005-02-02         135         54          0           0           25          214
aa         VH010      2005-02-03         214         0           10          0           0           204
aa         VH010      2005-03-01         204         12          0           0           0           216bb         VT002      2005-02-02         0           95          0           0           0           95
bb         VT002      2005-02-03         95          0           0           65          0           30
bb         VT002      2005-02-05         30          0           20          0           15          25
bb         VT002      2005-02-07         25          100         0           0           0           125
bb         VT002      2005-03-07         125         0           0           50          0           75
cc         VT003      2005-02-07         0           100         0           0           0           100

Answer 3

物料编码     供应商编号   日期               初存量         入库          退货          发料          退料         结存量
--------   ---------  ----------       ----------   ----------  ----------- ----------- ----------  ---------
aa         VT001      2005-02-01         0           180         0           55          0           125
aa         VT001      2005-02-02         125         200         0           0           10          335
aa         VT001      2005-02-03         335         0           5           0           0           330
aa         VH010      2005-02-01         0           150         0           15          0           135
aa         VH010      2005-02-02         135         54          0           0           25          214
aa         VH010      2005-02-03         214         0           10          0           0           204
aa         VH010      2005-03-01         204         12          0           0           0           216bb         VT002      2005-02-02         0           95          0           0           0           95
bb         VT002      2005-02-03         95          0           0           65          0           30
bb         VT002      2005-02-05         30          0           20          0           15          25
bb         VT002      2005-02-07         25          100         0           0           0           125
bb         VT002      2005-03-07         125         0           0           50          0           75
cc         VT003      2005-02-07         0           100         0           0           0           100

Answer 4

--结存表
CREATE TABLE Stocks(序号 int IDENTITY PRIMARY KEY,物料编码 varchar(10),供应商编号 varchar(10),结存年月 int,结存量 int)
INSERT Stocks SELECT 'aa','VT001',NULL,0
INSERT Stocks SELECT 'aa','VH010',NULL,0
UNION  ALL    SELECT 'bb','VT002',NULL,0
UNION  ALL    SELECT 'cc','VT003',NULL,0--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
物料编码 varchar(10),  --产品编号
供应商编号 varchar(10),--供应商编码
数量 int,      --交易数量
类型 int,          --交易标志,0代表入库,1代表出库,2代表退料,3代表退货.
日期 datetime)     --交易日期
INSERT tb SELECT 'aa','VT001',100,0,'2005-1-1'
UNION ALL SELECT 'aa','VT001',90 ,0,'2005-2-1'
UNION ALL SELECT 'aa','VT001',55 ,1,'2005-2-1'
UNION ALL SELECT 'aa','VT001',10,2,'2005-2-2'
UNION ALL SELECT 'aa','VT001',5 ,3,'2005-2-3'
UNION ALL SELECT 'aa','VT001',200,0,'2005-2-2'
UNION ALL SELECT 'aa','VT001',90 ,0,'2005-2-1' 
UNION ALL SELECT 'aa','VH010',150 ,0,'2005-2-1'
UNION ALL SELECT 'aa','VH010',15 ,1,'2005-2-1'
UNION ALL SELECT 'aa','VH010',25,2,'2005-2-2'
UNION ALL SELECT 'aa','VH010',10 ,3,'2005-2-3'
UNION ALL SELECT 'aa','VH010',54,0,'2005-2-2'
UNION ALL SELECT 'aa','VH010',12 ,0,'2005-3-1'UNION ALL SELECT 'bb','VT002',95 ,0,'2005-2-2'
UNION ALL SELECT 'bb','VT002',65 ,1,'2005-2-3'
UNION ALL SELECT 'bb','VT002',15,2,'2005-2-5'
UNION ALL SELECT 'bb','VT002',20,3,'2005-2-5'
UNION ALL SELECT 'bb','VT002',100,0,'2005-2-7'
UNION ALL SELECT 'bb','VT002',50,1,'2005-3-7'
UNION ALL SELECT 'cc','VT003',100,0,'2005-2-7'
GO--查询时间段定义
DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate='2005-2-1',@EndDate='2005-4-1'--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @Proiod int,@dt datetime
SELECT @Proiod=CONVERT(CHAR(6),DATEADD(Month,-1,@StartDate),112),
    @dt=DATEADD(Day,1-Day(@StartDate),@StartDate)
;with cte 
as
(
select 物料编码,供应商编号,日期=CONVERT(char(10),日期,120),
   sum(case when 类型 = 0 then 数量 else 0 end ) as 入库,
   sum(case when 类型 = 3 then 数量 else 0 end ) as 退货,
   sum(case when 类型 = 1 then 数量 else 0 end ) as 发料,
   sum(case when 类型 = 2 then 数量 else 0 end ) as 退料,
   sum(case when 类型 = 2 or 类型 = 0 then 数量 else -1 * 数量 end) 末存量,
       rid = row_number() over (partition by 物料编码,供应商编号 
                          order by CONVERT(char(10),日期,120))
from tb 
where 日期>=@StartDate AND 日期<DATEADD(Day,1,@EndDate)
group by 物料编码,供应商编号,CONVERT(char(10),日期,120)
),
cte1 
as
(
    select 物料编码,
       供应商编号,
       日期 ,
       初存量 = isnull((select sum(末存量) from cte where 物料编码 = a.物料编码 and 供应商编号 = a.供应商编号 and rid < a.rid  ),0),
       入库=ISNULL( 入库,0),
退货=ISNULL( 退货,0),
发料=ISNULL( 发料,0),
退料=ISNULL( 退料,0),末存量
    from cte a
)
select 物料编码,
       供应商编号,
       日期 ,
       初存量,
       入库=ISNULL( 入库,0),
退货=ISNULL( 退货,0),
发料=ISNULL( 发料,0),
退料=ISNULL( 退料,0),
       末存量 = 末存量+初存量 
from cte1 
 
go
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'Stocks')
   DROP Table Stocks
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tb')
   DROP Table tb
GO
/*
物料编码    供应商编号  日期              初存量        入库          退货          发料          退料        结存量 
--------  ---------  ----------      ----------  ----------  ----------- ----------- ----------  --------- 
aa        VT001      2005-02-01        0          180        0          55          0          125 
aa        VT001      2005-02-02        125        200        0          0          10          335 
aa        VT001      2005-02-03        335        0          5          0          0          330 
aa        VH010      2005-02-01        0          150        0          15          0          135 
aa        VH010      2005-02-02        135        54          0          0          25          214 
aa        VH010      2005-02-03        214        0          10          0          0          204 
aa        VH010      2005-03-01        204        12          0          0          0          216
bb        VT002      2005-02-02        0          95          0          0          0          95 
bb        VT002      2005-02-03        95          0          0          65          0          30 
bb        VT002      2005-02-05        30          0          20          0          15          25 
bb        VT002      2005-02-07        25          100        0          0          0          125 
bb        VT002      2005-03-07        125        0          0          50          0          75 
cc        VT003      2005-02-07        0          100        0          0          0          100 
*/

Answer 5

--结存表
CREATE TABLE Stocks(序号 int IDENTITY PRIMARY KEY,物料编码 varchar(10),供应商编号 varchar(10),结存年月 int,结存量 int)
INSERT Stocks SELECT 'aa','VT001',NULL,0
INSERT Stocks SELECT 'aa','VH010',NULL,0
UNION  ALL    SELECT 'bb','VT002',NULL,0
UNION  ALL    SELECT 'cc','VT003',NULL,0--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
物料编码 varchar(10),  --产品编号
供应商编号 varchar(10),--供应商编码
数量 int,      --交易数量
类型 int,          --交易标志,0代表入库,1代表出库,2代表退料,3代表退货.
日期 datetime)     --交易日期
INSERT tb SELECT 'aa','VT001',100,0,'2005-1-1'
UNION ALL SELECT 'aa','VT001',90 ,0,'2005-2-1'
UNION ALL SELECT 'aa','VT001',55 ,1,'2005-2-1'
UNION ALL SELECT 'aa','VT001',10,2,'2005-2-2'
UNION ALL SELECT 'aa','VT001',5 ,3,'2005-2-3'
UNION ALL SELECT 'aa','VT001',200,0,'2005-2-2'
UNION ALL SELECT 'aa','VT001',90 ,0,'2005-2-1'UNION ALL SELECT 'aa','VH010',150 ,0,'2005-2-1'
UNION ALL SELECT 'aa','VH010',15 ,1,'2005-2-1'
UNION ALL SELECT 'aa','VH010',25,2,'2005-2-2'
UNION ALL SELECT 'aa','VH010',10 ,3,'2005-2-3'
UNION ALL SELECT 'aa','VH010',54,0,'2005-2-2'
UNION ALL SELECT 'aa','VH010',12 ,0,'2005-3-1'UNION ALL SELECT 'bb','VT002',95 ,0,'2005-2-2'
UNION ALL SELECT 'bb','VT002',65 ,1,'2005-2-3'
UNION ALL SELECT 'bb','VT002',15,2,'2005-2-5'
UNION ALL SELECT 'bb','VT002',20,3,'2005-2-5'
UNION ALL SELECT 'bb','VT002',100,0,'2005-2-7'
UNION ALL SELECT 'bb','VT002',50,1,'2005-3-7'
UNION ALL SELECT 'cc','VT003',100,0,'2005-2-7'
GO--查询时间段定义
DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate='2005-2-1',@EndDate='2005-4-1'--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @Proiod int,@dt datetime
SELECT @Proiod=CONVERT(CHAR(6),DATEADD(Month,-1,@StartDate),112),
    @dt=DATEADD(Day,1-Day(@StartDate),@StartDate)
--查询期初库存
SELECT
    物料编码=ISNULL(a.物料编码,b.物料编码),
    供应商编号=ISNULL(a.供应商编号,b.供应商编号),
    日期=ISNULL(b.日期,CONVERT(char(10),@StartDate,120)),
    初存量=0,
    入库=ISNULL(b.入库,0),
    退货=ISNULL(b.退货,0),
    发料=ISNULL(b.发料,0),
    退料=ISNULL(b.退料,0),
    结存量=0
INTO #T
FROM(
    --期初数
    SELECT 物料编码,供应商编号,结存量 FROM Stocks WHERE 结存年月=@Proiod
)a FULL JOIN(
    --统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)
    SELECT 
        物料编码,
        供应商编号,
        日期=CONVERT(char(10),@StartDate,120),    
        入库=0,
        退货=0,
        发料=0,
        退料=0
    FROM tb a
    WHERE 日期>=@dt AND 日期<@StartDate 
        AND NOT EXISTS(
            SELECT * FROM tb WHERE 物料编码=a.物料编码 AND 供应商编号=a.供应商编号 AND 日期>@StartDate AND 日期<DATEADD(Day,1,@EndDate))
    GROUP BY 物料编码,供应商编号
    UNION ALL
    --指定时间段内有交易发生的数据
    SELECT 
        物料编码,
        供应商编号,
        日期=CONVERT(char(10),日期,120),    
        入库=SUM(CASE WHEN 类型=0 THEN 数量 END),
        退货=SUM(CASE WHEN 类型=3  THEN 数量 END),
        发料=SUM(CASE WHEN 类型=1 THEN 数量 END),
        退料=SUM(CASE WHEN 类型=2 THEN 数量 END)
    FROM tb a
    WHERE 日期>=@StartDate AND 日期<DATEADD(Day,1,@EndDate)
    GROUP BY CONVERT(char(10),日期,120),物料编码,供应商编号
)b ON a.物料编码=b.物料编码 And a.供应商编号=b.供应商编号
ORDER BY 物料编码,供应商编号,日期;DECLARE @物料编码 varchar(10),@供应商编号 varchar(10);
DECLARE @初存量 INT,@结存量 INT;UPDATE #T SET
    @初存量=CASE WHEN 物料编码=@物料编码 AND 供应商编号=@供应商编号
                   THEN @结存量 ELSE 0 END,
    @结存量=CASE WHEN 物料编码=@物料编码 AND 供应商编号=@供应商编号
                   THEN @初存量+入库-退货-发料+退料 
                 ELSE 入库-退货-发料+退料 END,
    初存量=@初存量,
    结存量=@结存量,
    @物料编码=物料编码,
    @供应商编号=供应商编号;SELECT * FROM #T ORDER BY 物料编码,供应商编号 DESC,日期;GO
DROP TABLE #T;
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'Stocks')
   DROP Table Stocks
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tb')
   DROP Table tb
GO/*
物料编码       供应商编号      日期         初存量         入库          退货          发料          退料          结存量
---------- ---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
aa         VT001      2005-02-01 0           180         0           55          0           125
aa         VT001      2005-02-02 125         200         0           0           10          335
aa         VT001      2005-02-03 335         0           5           0           0           330
aa         VH010      2005-02-01 0           150         0           15          0           135
aa         VH010      2005-02-02 135         54          0           0           25          214
aa         VH010      2005-02-03 214         0           10          0           0           204
aa         VH010      2005-03-01 204         12          0           0           0           216
bb         VT002      2005-02-02 0           95          0           0           0           95
bb         VT002      2005-02-03 95          0           0           65          0           30
bb         VT002      2005-02-05 30          0           20          0           15          25
bb         VT002      2005-02-07 25          100         0           0           0           125
bb         VT002      2005-03-07 125         0           0           50          0           75
cc         VT003      2005-02-07 0           100         0           0           0           100(13 row(s) affected)*/

Answer 6

楼主只是少一个条件：
--指定时间段内有交易发生的数据
    SELECT 
        物料编码,
        供应商编号,
        日期=CONVERT(char(10),日期,120),    
        初存量=(SELECT SUM(CASE WHEN 类型=0 OR 类型=2  THEN 数量 ELSE -数量 END)
            FROM tb WHERE 供应商编号 = a.供应商编号 and 物料编码=a.物料编码 AND 日期>=@dt AND 日期<MIN(a.日期)),--结存表
CREATE TABLE Stocks(序号 int IDENTITY PRIMARY KEY,物料编码 varchar(10),供应商编号 varchar(10),结存年月 int,结存量 int)
INSERT Stocks SELECT 'aa','VT001',NULL,0
INSERT Stocks SELECT 'aa','VH010',NULL,0
UNION  ALL    SELECT 'bb','VT002',NULL,0
UNION  ALL    SELECT 'cc','VT003',NULL,0--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
物料编码 varchar(10),  --产品编号
供应商编号 varchar(10),--供应商编码
数量 int,      --交易数量
类型 int,          --交易标志,0代表入库,1代表出库,2代表退料,3代表退货.
日期 datetime)     --交易日期
INSERT tb SELECT 'aa','VT001',100,0,'2005-1-1'
UNION ALL SELECT 'aa','VT001',90 ,0,'2005-2-1'
UNION ALL SELECT 'aa','VT001',55 ,1,'2005-2-1'
UNION ALL SELECT 'aa','VT001',10,2,'2005-2-2'
UNION ALL SELECT 'aa','VT001',5 ,3,'2005-2-3'
UNION ALL SELECT 'aa','VT001',200,0,'2005-2-2'
UNION ALL SELECT 'aa','VT001',90 ,0,'2005-2-1' 
UNION ALL SELECT 'aa','VH010',150 ,0,'2005-2-1'
UNION ALL SELECT 'aa','VH010',15 ,1,'2005-2-1'
UNION ALL SELECT 'aa','VH010',25,2,'2005-2-2'
UNION ALL SELECT 'aa','VH010',10 ,3,'2005-2-3'
UNION ALL SELECT 'aa','VH010',54,0,'2005-2-2'
UNION ALL SELECT 'aa','VH010',12 ,0,'2005-3-1'UNION ALL SELECT 'bb','VT002',95 ,0,'2005-2-2'
UNION ALL SELECT 'bb','VT002',65 ,1,'2005-2-3'
UNION ALL SELECT 'bb','VT002',15,2,'2005-2-5'
UNION ALL SELECT 'bb','VT002',20,3,'2005-2-5'
UNION ALL SELECT 'bb','VT002',100,0,'2005-2-7'
UNION ALL SELECT 'bb','VT002',50,1,'2005-3-7'
UNION ALL SELECT 'cc','VT003',100,0,'2005-2-7'
GO--查询时间段定义
DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate='2005-2-1',@EndDate='2005-4-1'--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @Proiod int,@dt datetime
SELECT @Proiod=CONVERT(CHAR(6),DATEADD(Month,-1,@StartDate),112),
    @dt=DATEADD(Day,1-Day(@StartDate),@StartDate)--查询期初库存
SELECT
    物料编码=ISNULL(a.物料编码,b.物料编码),
    供应商编号=ISNULL(a.供应商编号,b.供应商编号),
    日期=ISNULL(b.日期,CONVERT(char(10),@StartDate,120)),
    初存量=ISNULL(a.结存量,0)+ISNULL(b.初存量,0),
    入库=ISNULL(b.入库,0),
    退货=ISNULL(b.退货,0),
    发料=ISNULL(b.发料,0),
    退料=ISNULL(b.退料,0),
    结存量=ISNULL(a.结存量,0)+ISNULL(b.初存量,0)+ISNULL(b.末存量,0)
FROM(
    --期初数
    SELECT 物料编码,供应商编号,结存量 FROM Stocks WHERE 结存年月=@Proiod
)a 
FULL JOIN(
    --统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)
    SELECT 
        物料编码,
        供应商编号,
        日期=CONVERT(char(10),@StartDate,120),    
        初存量=SUM(CASE WHEN (类型=0 OR 类型=2) THEN 数量 ELSE -数量 END),
        入库=0,
        退货=0,
        发料=0,
        退料=0,
        末存量=0
    FROM tb a
    WHERE 日期>=@dt AND 日期<@StartDate 
        AND NOT EXISTS(
            SELECT * FROM tb WHERE 物料编码=a.物料编码 AND 供应商编号=a.供应商编号 AND 日期>@StartDate AND 日期<DATEADD(Day,1,@EndDate))
    GROUP BY 物料编码,供应商编号
    UNION ALL
    --指定时间段内有交易发生的数据
    SELECT 
        物料编码,
        供应商编号,
        日期=CONVERT(char(10),日期,120),    
        初存量=(SELECT SUM(CASE WHEN 类型=0 OR 类型=2  THEN 数量 ELSE -数量 END)
            FROM tb WHERE 供应商编号 = a.供应商编号 and 物料编码=a.物料编码 AND 日期>=@dt AND 日期<MIN(a.日期)),
        入库=SUM(CASE WHEN 类型=0 THEN 数量 END),
        退货=SUM(CASE WHEN 类型=3  THEN 数量 END),
        发料=SUM(CASE WHEN 类型=1 THEN 数量 END),
        退料=SUM(CASE WHEN 类型=2 THEN 数量 END),
        末存量=SUM(CASE WHEN 类型=0 OR 类型=2 THEN 数量 ELSE -数量 END)
    FROM tb a
    WHERE 日期>=@StartDate AND 日期<DATEADD(Day,1,@EndDate)
    GROUP BY CONVERT(char(10),日期,120),物料编码,供应商编号
)b ON a.物料编码=b.物料编码 And a.供应商编号=b.供应商编号
ORDER BY 物料编码,供应商编号,日期 
go
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'Stocks')
   DROP Table Stocks
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tb')
   DROP Table tb
GO
/*
物料编码    供应商编号  日期              初存量        入库          退货          发料          退料        结存量 
--------  ---------  ----------      ----------  ----------  ----------- ----------- ----------  --------- 
aa        VT001      2005-02-01        0          180        0          55          0          125 
aa        VT001      2005-02-02        125        200        0          0          10          335 
aa        VT001      2005-02-03        335        0          5          0          0          330 
aa        VH010      2005-02-01        0          150        0          15          0          135 
aa        VH010      2005-02-02        135        54          0          0          25          214 
aa        VH010      2005-02-03        214        0          10          0          0          204 
aa        VH010      2005-03-01        204        12          0          0          0          216
bb        VT002      2005-02-02        0          95          0          0          0          95 
bb        VT002      2005-02-03        95          0          0          65          0          30 
bb        VT002      2005-02-05        30          0          20          0          15          25 
bb        VT002      2005-02-07        25          100        0          0          0          125 
bb        VT002      2005-03-07        125        0          0          50          0          75 
cc        VT003      2005-02-07        0          100        0          0          0          100 
*/

Answer 7

;With China as 
(
   select 物料编码,供应商编号,日期,入库=isnull(sum(case when 类型=0 then 数量 end),0),
   入库退货=isnull(sum(case when 类型=3 then 数量 end),0),
   发料=isnull(sum(case when 类型=1 then 数量 end),0),
   发料退货=isnull(sum(case when 类型=2 then 数量 end),0)
   from tb 
   group by 物料编码,供应商编号,日期 
),china1 as 
(
  select px=row_number()over(order by 物料编码), *,结存量=(select sum(入库-入库退货-发料+发料退货) from china where 
  物料编码=a.物料编码 and 供应商编号=a.供应商编号 and 日期<=a.日期)  from China a  
),china2 as
(
  select 初存量= isnull((select 结存量 from china1 where px=a.px-1 and 供应商编号=a.供应商编号),0),物料编码,供应商编号,日期,入库,
  入库退货,发料,发料退货,结存量 from china1 a 
)
select * from china2初存量         物料编码       供应商编号      日期                      入库          入库退货        发料          发料退货        结存量
----------- ---------- ---------- ----------------------- ----------- ----------- ----------- ----------- -----------
0           aa         VH010      2005-02-01 00:00:00.000 150         0           15          0           135
135         aa         VH010      2005-02-02 00:00:00.000 54          0           0           25          214
214         aa         VH010      2005-02-03 00:00:00.000 0           10          0           0           204
204         aa         VH010      2005-03-01 00:00:00.000 12          0           0           0           216
0           aa         VT001      2005-01-01 00:00:00.000 100         0           0           0           100
100         aa         VT001      2005-02-01 00:00:00.000 180         0           55          0           225
225         aa         VT001      2005-02-02 00:00:00.000 200         0           0           10          435
435         aa         VT001      2005-02-03 00:00:00.000 0           5           0           0           430
0           bb         VT002      2005-02-02 00:00:00.000 95          0           0           0           95
95          bb         VT002      2005-02-03 00:00:00.000 0           0           65          0           30
30          bb         VT002      2005-02-05 00:00:00.000 0           20          0           15          25
25          bb         VT002      2005-02-07 00:00:00.000 100         0           0           0           125
125         bb         VT002      2005-03-07 00:00:00.000 0           0           50          0           75
0           cc         VT003      2005-02-07 00:00:00.000 100         0           0           0           100
(14 行受影响)

Answer 8

现在的新问题是:
怎么得出最后一次结存记录呢?
比如上述测试结果我希望得到:物料编码    供应商编号  日期              初存量        入库          退货          发料          退料        结存量 
--------  ---------  ----------      ----------  ----------  ----------- ----------- ----------  --------- 
aa        VT001      2005-02-03        335        0          5          0          0          330 
aa        VH010      2005-03-01        204        12          0          0          0          216
bb        VT002      2005-03-07        125        0          0          50          0          75 
cc        VT003      2005-02-07        0          100        0          0          0          100