库存盘点收发存统计用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

回复赞