高分求复杂结存SQL算法!

Question

T2  2009-2-2    M001      S001        SO001        D01  120    0    10    5    0    0    0    115  PCS 
T2  2009-2-2    M001      S001        SO001        D01  115    0    0    0    50    0    10    55  PCS 这两条差不多啊,那要怎么个汇总法?

Answer 1

这个写错了,应该是
T2  2009-2-2    M001      S001        SO001        D01  120    0    10    5    0    0    0    115  PCS
T3  2009-2-3    M001      S001        SO001        D01  115    0    0    0    50    0    10    55  PCS

Answer 2

大概写下
select a.你要的字段,b.你要的字段 from MaterialIOMaster a  inner join MaterialTransferMaster bon a.id=b.id inner join 其它表 d on a.id=d.id    .....  where a.date between 'StartDate' and 'EndDate'

Answer 3

最好的办法,每个表放四五条典型的数据,最好你能把表create出来,数据insert进去,想要的结果列出来,这样解决起来才快.

Answer 4

select a.你要的字段,b.你要的字段 from MaterialIOMaster a  inner join MaterialTransferMaster bon a.id=b.id inner join 其它表 d on a.id=d.id    .....  where a.date between 'StartDate' and 'EndDate'

Answer 5

调了半天,就快要实际了!可还是不能区分仓库(MaterialIOMaster.Depot = MaterialStock.Depot).导致日期错误.请大家帮忙改改,还有怎么加入调拨单的明细账到此统计中?下面是测试SQL:
USE CIMS_Debug
GOIF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'MaterialBase')
   DROP Table MaterialBase
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'Stocks')
   DROP Table Stocks
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tbm')
   DROP Table tbm
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tb')
   DROP Table tb
GOCREATE TABLE MaterialBase(物料 varchar(10),描述 varchar(255))
INSERT MaterialBase SELECT 'aa','Test'
UNION  ALL    SELECT 'bb','0805,10K,+/-5%'
UNION  ALL    SELECT 'cc',NULLCREATE TABLE Stocks(物料 varchar(10),供应商 varchar(15),供应商型号 varchar(50),仓库 varchar(10),结存 int)
INSERT Stocks SELECT 'aa','S01','S001','d01',100
INSERT Stocks SELECT 'aa','S01','S001','d02',50
UNION  ALL    SELECT 'bb','S02','S0021','d01',80
UNION  ALL    SELECT 'cc','S02','S0021','d01',50--明细账数据
CREATE TABLE tbm(单号 varchar(15)PRIMARY KEY,仓库 varchar(10),日期 datetime)INSERT tbm SELECT 'D01','d01','2005-1-1'
UNION ALL SELECT 'D02','d02','2005-2-2'
UNION ALL SELECT 'D03','d01','2005-2-7'
UNION ALL SELECT 'D04','d01','2005-1-8'
UNION ALL SELECT 'D05','d01','2005-3-8'
UNION ALL SELECT 'D06','d01','2005-2-5'--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
单号 varchar(15),  --单号
物料 varchar(10),  --产品编号
供应商 varchar(15),
供应商型号 varchar(50),
数量 int,      --交易数量
Flag tinyint)      --交易标志,0代表入库,1代表发料,2代表退料,3代表退货,4代表报废,这样可以有效区分退货(-)INSERT tb SELECT 'D01','aa','S01','S001',50,0
UNION ALL SELECT 'D01','aa','S01','S001',90,0
UNION ALL SELECT 'D01','aa','S01','S001',55 ,1
UNION ALL SELECT 'D02','aa','S01','S001',10,2
UNION ALL SELECT 'D02','aa','S01','S001',5 ,4
UNION ALL SELECT 'D02','aa','S01','S001',200,0
UNION ALL SELECT 'D02','aa','S01','S001',90 ,3
UNION ALL SELECT 'D02','bb','S02','S0021',95 ,0
UNION ALL SELECT 'D03','bb','S02','S0021',65 ,1
UNION ALL SELECT 'D03','bb','S02','S0021',15,0
UNION ALL SELECT 'D03','bb','S02','S0021',20,3
UNION ALL SELECT 'D03','bb','S02','S0021',10,0
UNION ALL SELECT 'D04','cc','S02','S0021',40,0
UNION ALL SELECT 'D05','cc','S02','S0021',25,1
UNION ALL SELECT 'D05','cc','S02','S0021',25,3
UNION ALL SELECT 'D06','cc','S02','S0021',5,2
UNION ALL SELECT 'D06','cc','S02','S0021',3,4
--查询时间段定义
DECLARE @dt1 smalldatetime,@dt2 smalldatetime
SELECT @dt1='2005-1-1',@dt2='2005-3-10'--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @dt smalldatetime
SELECT @dt=DATEADD(Day,1-Day(@dt1),@dt1)--查询期初库存
SELECT 
物料=ISNULL(a.物料,b.物料),
日期=ISNULL(b.日期,@dt1),
供应商=ISNULL(a.供应商,b.供应商),
供应商型号=ISNULL(a.供应商型号,b.供应商型号),
仓库=ISNULL(a.仓库,b.仓库),
    初存=ISNULL(a.结存,0)+ISNULL(b.初存,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)a  --期初数 
FULL JOIN(
SELECT 
a.物料,
    c.日期,    
    a.供应商,
a.供应商型号,
c.仓库,
初存=(SELECT SUM(CASE WHEN Flag=0 OR Flag=2 THEN tb.数量 ELSE -tb.数量 END)
        FROM tb,tbm WHERE  tbm.日期>=@dt AND tbm.日期<MIN(c.日期) AND tb.单号 = tbm.单号 AND tb.物料=a.物料 And tb.供应商 = a.供应商 And tb.供应商型号 = a.供应商型号),
    入库=SUM(CASE WHEN Flag=0 THEN 数量 END),
发料=SUM(CASE WHEN Flag=1 THEN 数量 END),
    退料=SUM(CASE WHEN Flag=2 THEN 数量 END),
    退货=SUM(CASE WHEN Flag=3 THEN 数量 END),
    报废=SUM(CASE WHEN Flag=4 THEN 数量 END),
    末存=SUM(CASE WHEN Flag=0 OR Flag=2 THEN 数量 ELSE -数量 END)
FROM tb a,tbm c 
WHERE c.日期>=@dt1 AND c.日期<DATEADD(Day,1,@dt2) And c.单号 = a.单号
GROUP BY c.日期,物料,供应商,供应商型号,c.仓库
)b ON a.物料=b.物料 And a.供应商 = b.供应商 And a.供应商型号 = b.供应商型号
ORDER BY 物料,供应商,供应商型号
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'MaterialBase')
   DROP Table MaterialBase
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'Stocks')
   DROP Table Stocks
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tbm')
   DROP Table tbm
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tb')
   DROP Table tb
GO

Answer 6

--查询时间段定义
DECLARE @dt1 smalldatetime,@dt2 smalldatetime
SELECT @dt1='2005-1-1',@dt2='2005-3-10'--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @dt smalldatetime
SELECT @dt=DATEADD(Day,1-Day(@dt1),@dt1)--查询期初库存
SELECT 
物料=ISNULL(a.物料,b.物料),
日期=ISNULL(b.日期,@dt1),
供应商=ISNULL(a.供应商,b.供应商),
供应商型号=ISNULL(a.供应商型号,b.供应商型号),
仓库=ISNULL(a.仓库,b.仓库),
    初存=ISNULL(a.结存,0)+ISNULL(b.初存,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)a  --期初数 
FULL JOIN(
SELECT 
a.物料,
    c.日期,    
    a.供应商,
a.供应商型号,
c.仓库,
初存=(SELECT SUM(CASE WHEN Flag=0 OR Flag=2 THEN tb.数量 ELSE -tb.数量 END)
        FROM tb,tbm WHERE  tbm.日期>=@dt AND tbm.日期<MIN(c.日期) AND tb.单号 = tbm.单号 AND tb.物料=a.物料 And tb.供应商 = a.供应商 And tb.供应商型号 = a.供应商型号),
    入库=SUM(CASE WHEN Flag=0 THEN 数量 END),
发料=SUM(CASE WHEN Flag=1 THEN 数量 END),
    退料=SUM(CASE WHEN Flag=2 THEN 数量 END),
    退货=SUM(CASE WHEN Flag=3 THEN 数量 END),
    报废=SUM(CASE WHEN Flag=4 THEN 数量 END),
    末存=SUM(CASE WHEN Flag=0 OR Flag=2 THEN 数量 ELSE -数量 END)
FROM tb a,tbm c 
WHERE c.日期>=@dt1 AND c.日期<DATEADD(Day,1,@dt2) And c.单号 = a.单号
GROUP BY c.日期,物料,供应商,供应商型号,c.仓库
)b ON a.物料=b.物料 And a.供应商 = b.供应商 And a.供应商型号 = b.供应商型号
ORDER BY 物料,供应商,供应商型号