求：一条SQL语句搞定盘点库存！！！！！！

[sundgy]

表结构如下：
材料信息表
材料编码 材料名称 材料规格 单位
001 WMS电阻 50R 个

材料上期盘点表
材料编码 材料名称 材料规格 单位 上期存量 结存日期
001 WMS电阻 50R 个 10 2010-04-30

材料入仓表
材料编码 材料名称 材料规格 单位 数量 日期
001 WMS电阻 50R 个 10 2010-05-4
001 WMS电阻 50R 个 15 2010-05-8
材料出仓表
材料编码 材料名称 材料规格 单位 数量 日期
001 WMS电阻 50R 个 20 2010-05-10

求目前库存（上期盘点+入仓-出仓）
材料编码 材料名称 材料规格 单位 数量
001 WMS电阻 50R 个 15

求教SQL写法，路过顶有分！多谢！

[sundgy]

有高手已经解决，路过人接分，结贴

[merrymin]

GXLZ
JF

[jhldelphi]

接分。。。。。

[柯本]

路过...

[梦幻人生]

路过...

[happy_tiger]

路过...

[zzj850902]

接分 求答案

[haerbinlg]

鲁 国

[stgzdcc]

jf~~~

[xmfan2000]

jf~~~~~~~~~~~~~~~~~~~~~

[winstonbonaparte]

create table WL(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20))
go
insert into WL
values('001','WMS电阻','50R','个')
go
create table SPD(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20),SQShL int,RQ datetime)
go
insert into SPD
values('001','WMS电阻','50R','个',10,'2010-04-30')
go
create table RK(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20),RKShL int,RQ datetime)
go
insert into RK
values('001','WMS电阻','50R','个',10,'2010-05-04')
go
insert into RK
values('001','WMS电阻','50R','个',15,'2010-05-08')
go
create table CK(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20),CKShL int,RQ datetime)
go
insert into CK
values('001','WMS电阻','50R','个',20,'2010-05-10')
go

select A.DH,MCh,GG,DW,KC = SQShL + RKShL - CKShL
from (
    select DH,SQShL = sum(SQShL),RKShL = sum(RKShL),CKShL = sum(CKShL)
    from (
        select DH,SQShL = sum(SQShL),RKShL = 0,CKShL = 0
        from SPD 
        group by DH
        union all
        select DH,SQShL = 0,RKShL = sum(RKShL),CKShL = 0
        from RK
        group by DH
        union all
        select DH,SQShL = 0,RKShL = 0,CKShL = sum(CKShL)
        from CK
        group by DH
    )A
    group by DH
)A,WL B
where A.DH = B.DH
go
drop table WL
go
drop table SPD
go
drop table RK
go
drop table CK
--運行結果：
001 WMS电阻	50R	个	15	

[wujinyuan]

接分。。。。。

[shanxmxj]

路过...

[e8soft]

捡分来了，

像这样的情况。 都要用到聚合 和UNION ALL

[想到才能做到]

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 18 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'CostProducts'. Scan count 1, logical reads 73, physical reads 0, read-ahead reads 0.


/*貨倉存貨進銷存總表*/
ALTER PROCEDURE up_jxc_01
(
@wID1 CHAR(6)='',
@wID2 CHAR(6)='Z',
@Barcode1 CHAR(38)='',
@Barcode2 CHAR(38)='Z',
@Date1 DATETIME='',
@Date2 DATETIME='',
@wType1 CHAR(10)='',
@wType2 CHAR(10)='Z',
@pType1 CHAR(10)='',
@pType2 CHAR(10)='Z'
)
AS
SET NOCOUNT ON
------------------------------
-- 篩選產品料號數據
------------------------------
SELECT a.*
INTO #CostProducts
FROM CostProducts a
WHERE a.ProductBarcode BETWEEN @Barcode1 AND @Barcode2
AND a.ProductType BETWEEN @pType1 AND @pType2
AND a.sh=1
/*當提取行超過500行時建立索引*/
IF @@ROWCOUNT>500
BEGIN
CREATE INDEX idx_barcode
ON #CostProducts (ProductBarcode)
END

------------------------------
-- 存上結餘數據
------------------------------
/*期初數據*/
SELECT a.Wid,c.wName,a.ProductBarcode,a.Qty,a.Qty*a.Weight AS Weight,a.Qty*a.Area AS Area
INTO #tem1 --生成臨時表
FROM Qichu a
INNER JOIN #CostProducts b
ON a.ProductBarcode=b.ProductBarcode
INNER JOIN Warehouse c
ON a.Wid=c.Wid
WHERE a.Wid BETWEEN @wId1 AND @wId2
AND c.wType BETWEEN @wType1 AND @wType2
UNION ALL
/*收料結報單數據*/
SELECT b.Wid,c.wName,a.ProductBarcode,a.Qty,a.Weight,a.Area
FROM InDesc a
INNER JOIN SumIn b
ON a.InNo=b.InNo
INNER JOIN Warehouse c
ON b.Wid=c.Wid
INNER JOIN #CostProducts d
ON a.ProductBarcode=d.ProductBarcode
WHERE b.sh=1 AND b.AddDate<=@Date1-1
AND b.Wid BETWEEN @wID1 AND @wID2
AND c.wType BETWEEN @wType1 AND @wType2
UNION ALL
/*收料退回單數據*/
SELECT b.Wid,c.wName,a.ProductBarcode,-a.Qty,-a.Weight,-a.Area
FROM InReturn a
INNER JOIN SumInReturn b
ON a.InReturnNo=b.InReturnNo
INNER JOIN Warehouse c
ON b.Wid=c.Wid
INNER JOIN #CostProducts d
ON a.ProductBarcode=d.ProductBarcode
WHERE b.sh=1 AND b.AddDate<=@Date1-1
AND b.Wid BETWEEN @wID1 AND @wID2
AND c.wType BETWEEN @wType1 AND @wType2
UNION ALL
...数据太长,此处省略
/*報廢單數據*/
SELECT b.Wid,c.wName,a.ProductBarcode,-a.Qty,-a.Weight,-a.Area
FROM Baofei a
INNER JOIN SumBaofei b
ON a.BaofeiNo=b.BaofeiNo
INNER JOIN Warehouse c
ON b.Wid=c.Wid
INNER JOIN #CostProducts d
ON a.ProductBarcode=d.ProductBarcode
WHERE b.sh=1 AND b.AddDate<=@Date1-1
AND b.Wid BETWEEN @wID1 AND @wID2
AND c.wType BETWEEN @wType1 AND @wType2

------------------------------
-- 本期發生數據
------------------------------
/*期初數據*/
SELECT a.Wid,a.wName,a.ProductBarcode,d.ProductType,d.ProductName,d.Unit,
a.Qty AS Qty,0.0000 AS InQty,0.0000 AS OutQty,0.0000 AS DiaoQty,0.0000 AS BaofeiQty,
a.Weight AS Weight,0.0000 AS InWeight,0.0000 AS OutWeight,0.0000 AS DiaoWeight,0.0000 AS BaofeiWeight,
a.Area AS Area,0.0000 AS InArea,0.0000 AS OutArea,0.0000 AS DiaoArea,0.0000 AS BaofeiArea
FROM #tem1 a
INNER JOIN #CostProducts d
ON a.ProductBarcode=d.ProductBarcode
UNION ALL
/*收料結報單數據*/
SELECT b.Wid,c.wName,a.ProductBarcode,d.ProductType,d.ProductName,d.Unit,
0.0000,a.Qty AS inQty,0.0000,0.0000,0.0000,--數量部分
0.0000,a.Weight AS inWeight,0.0000,0.0000,0.0000,--重量部分
0.0000,a.Area AS inArea,0.0000,0.0000,0.0000--面積部分
FROM InDesc a
INNER JOIN SumIn b
ON a.InNo=b.InNo
INNER JOIN Warehouse c
ON b.Wid=c.Wid
INNER JOIN #CostProducts d
ON a.ProductBarcode=d.ProductBarcode
WHERE b.sh=1
AND b.AddDate BETWEEN @Date1 AND @Date2
AND b.Wid BETWEEN @wID1 AND @wID2
AND c.wType BETWEEN @wType1 AND @wType2
UNION ALL
/*收料退回單數據*/
SELECT b.Wid,c.wName,a.ProductBarcode,d.ProductType,d.ProductName,d.Unit,
0.0000,0.0000,a.Qty AS OutQty,0.0000,0.0000,--數量部分
0.0000,0.0000,a.Weight AS OutWeight,0.0000,0.0000,--重量部分
0.0000,0.0000,a.Area AS OutArea,0.0000,0.0000--面積部分
FROM InReturn a
INNER JOIN SumInReturn b
ON a.InReturnNo=b.InReturnNo
INNER JOIN Warehouse c
ON b.Wid=c.Wid
INNER JOIN #CostProducts d
ON a.ProductBarcode=d.ProductBarcode
WHERE b.sh=1
AND b.AddDate BETWEEN @Date1 AND @Date2
AND b.Wid BETWEEN @wID1 AND @wID2
AND c.wType BETWEEN @wType1 AND @wType2
UNION ALL
/*領料單數據*/
...内容太长,省略
/*調整單數據*/
SELECT b.Wid,c.wName,a.ProductBarcode,d.ProductType,d.ProductName,d.Unit,
0.0000,0.0000,0.0000,a.Qty AS DiaoQty,0.0000,--數量部分
0.0000,0.0000,0.0000,a.Weight AS DiaoWeight,0.0000,--重量部分
0.0000,0.0000,0.0000,a.Area AS DiaoArea,0.0000--面積部分
FROM Diaozheng a
INNER JOIN SumDiaozheng b
ON a.DiaozhengNo=b.DiaozhengNo
INNER JOIN Warehouse c
ON b.Wid=c.Wid
INNER JOIN #CostProducts d
ON a.ProductBarcode=d.ProductBarcode
WHERE b.sh=1
AND b.AddDate BETWEEN @Date1 AND @Date2
AND b.Wid BETWEEN @wID1 AND @wID2
AND c.wType BETWEEN @wType1 AND @wType2
UNION ALL
/*報廢單數據*/
SELECT b.Wid,c.wName,a.ProductBarcode,d.ProductType,d.ProductName,d.Unit,
0.0000,0.0000,0.0000,0.0000,a.Qty AS BaofeiQty,--數量部分
0.0000,0.0000,0.0000,0.0000,a.Weight AS BaofeiWeight,--重量部分
0.0000,0.0000,0.0000,0.0000,a.Area AS BaofeiArea--面積部分
FROM Baofei a
INNER JOIN SumBaofei b
ON a.BaofeiNo=b.BaofeiNo
INNER JOIN Warehouse c
ON b.Wid=c.Wid
INNER JOIN #CostProducts d
ON a.ProductBarcode=d.ProductBarcode
WHERE b.sh=1
AND b.AddDate BETWEEN @Date1 AND @Date2
AND b.Wid BETWEEN @wID1 AND @wID2
AND c.wType BETWEEN @wType1 AND @wType2

------------------------------
-- 清除臨時數據
------------------------------
TRUNCATE TABLE #CostProducts
TRUNCATE TABLE #tem1
DROP TABLE #CostProducts
DROP TABLE #tem1

[songtreer]

gx,jf

[ganwendong]

我也来接分 呵呵

[sundgy]

有个高手一个函数就搞定，不佩服都不行！