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

[ck_cheng]

数据库结构：

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

[Tiger_Zhao]

-- 加些数据针对盘点时还没库存的情况
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

[江南小鱼]

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

[ck_cheng]

引用 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”写的