库存盘点收发存统计用sql语句怎么写

ck_cheng 2015-07-15 11:29:21


数据库结构:

create table WL(DH varchar(20),MCh varchar(20),GG varchar(20),DW varchar(20))
go
insert into WL
values('A10021040','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('A10021040','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('A10021040','WMS电阻','50R','个',10,'2010-05-04')
go
insert into RK
values('A10021040','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('A10021040','WMS电阻','50R','个',20,'2010-05-10')
go

...全文
208 点赞 收藏 3
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ck_cheng 2015-07-24
引用 1 楼 lovelj2012 的回复:
select w.DH as '材料编码',w.MCh as '材料名称',w.GG as '材料规格',w.DW as '单位'
,s.SQShL as '期初结存数',r.RKShL as '入库数',c.CKShL as '出库数'
,s.SQShL + r.RKShL - c.CKShL as '期末结存数',c.RQ as '单价日期'
from WL w
left join SPD s on w.DH = s.DH
left join RK r on w.DH = r.DH
left join CK c on w.DH = c.DH


你这种写法,结果是不对的

正确的写法测试暂时没有问题,如上面“Tiger_Zhao”写的
回复
江南小鱼 2015-07-15
select w.DH as '材料编码',w.MCh as '材料名称',w.GG as '材料规格',w.DW as '单位'
	,s.SQShL as '期初结存数',r.RKShL as '入库数',c.CKShL as '出库数'
	,s.SQShL + r.RKShL - c.CKShL as '期末结存数',c.RQ as '单价日期'
from WL w
left join SPD s on w.DH = s.DH
left join RK r on w.DH = r.DH
left join CK c on w.DH = c.DH
回复
Tiger_Zhao 2015-07-15
-- 加些数据针对盘点时还没库存的情况
insert into WL
values('A10021041','WMS电阻','100R','个')

insert into RK
values('A10021041','WMS电阻','100R','个',20,'2010-05-09')

;WITH START AS (
SELECT WL.*,
ISNULL(SPD.sqshl,0) qty,
ISNULL(RQ,'2010-04-30') RQ
FROM WL
LEFT JOIN SPD
ON WL.DH = SPD.DH
)
,DAILY AS (
SELECT ISNULL(RK.DH,CK.DH) DH,
ISNULL(RK.MCh,CK.MCh) MCh,
ISNULL(RK.GG,CK.GG) GG,
ISNULL(RK.DW,CK.DW) DW,
ISNULL(RK.RKShL,0) RKShL,
ISNULL(CK.CKShL,0) CKShL,
ISNULL(RK.RQ,CK.RQ) RQ,
ROW_NUMBER() OVER(PARTITION BY ISNULL(RK.DH,CK.DH)
ORDER BY ISNULL(RK.RQ,CK.RQ)
) rn
FROM RK
FULL JOIN CK
ON RK.DH = CK.DH
AND RK.RQ = CK.RQ
)
,R AS (
SELECT DH, MCh, GG, DW,
qty start_qty,
0 RKShL,
0 CKShL,
qty end_qty,
RQ,
CONVERT(bigint,0) rn
FROM START
UNION ALL
SELECT d.DH, d.MCh, d.GG, d.DW,
r.end_qty start_qty,
d.RKShL,
d.CKShL,
r.end_qty + d.RKShL - d.CKShL end_qty,
d.RQ,
d.rn
FROM r
JOIN DAILY d
ON r.DH = d.DH
AND r.rn + 1 = d.rn
)
SELECT *
FROM r
ORDER BY DH, rn

DH           MCh          GG     DW        start_qty       RKShL       CKShL     end_qty RQ             rn
------------ ------------ ------ ------- ----------- ----------- ----------- ----------- ---------- ------
A10021040 WMS电阻 50R 个 10 0 0 10 2010-04-30 0
A10021040 WMS电阻 50R 个 10 10 0 20 2010-05-04 1
A10021040 WMS电阻 50R 个 20 15 0 35 2010-05-08 2
A10021040 WMS电阻 50R 个 35 0 20 15 2010-05-10 3
A10021041 WMS电阻 100R 个 0 0 0 0 2010-04-30 0
A10021041 WMS电阻 100R 个 0 20 0 20 2010-05-09 1
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-07-15 11:29
社区公告
暂无公告