34,590
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
INPUTDATE DATETIME
,ITEMCODE VARCHAR(10)
,TYPE VARCHAR(10)
,NUM INT
)
INSERT INTO TB
SELECT '2010-05-01','A','期初结存', 200 UNION ALL
SELECT '2010-05-02','A','入库', 100 UNION ALL
SELECT '2010-05-03','A','出库', 50 UNION ALL
SELECT '2010-05-04','A','入库', 50 UNION ALL
SELECT '2010-05-05','A','出库', 20 UNION ALL
SELECT '2010-05-11','B','期初结存', 2000 UNION ALL
SELECT '2010-05-12','B','入库', 1000 UNION ALL
SELECT '2010-05-13','B','出库', 500 UNION ALL
SELECT '2010-05-14','B','入库', 500 UNION ALL
SELECT '2010-05-15','B','出库', 200
;WITH MU AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY ITEMCODE ORDER BY INPUTDATE) AS NID FROM TB
)
,MU2 AS (
SELECT NID,INPUTDATE,ITEMCODE,TYPE,NUM AS RU,0 AS CHU,NUM AS JIECUN FROM MU WHERE TYPE='期初结存'
UNION ALL
SELECT
T1.NID,T1.INPUTDATE,T1.ITEMCODE,T1.TYPE
,CASE WHEN T1.TYPE='入库' THEN T1.NUM ELSE 0 END
,CASE WHEN T1.TYPE='出库' THEN T1.NUM ELSE 0 END
,CASE WHEN T1.TYPE='入库' THEN T2.JIECUN+T1.NUM ELSE T2.JIECUN-T1.NUM END
FROM MU T1
INNER JOIN MU2 T2 ON T1.ITEMCODE=T2.ITEMCODE AND T1.NID=T2.NID+1
)
SELECT * FROM MU2
ORDER BY ITEMCODE,INPUTDATE
/*
NID INPUTDATE ITEMCODE TYPE RU CHU JIECUN
-------------------- ----------------------- ---------- ---------- ----------- ----------- -----------
1 2010-05-01 00:00:00.000 A 期初结存 200 0 200
2 2010-05-02 00:00:00.000 A 入库 100 0 300
3 2010-05-03 00:00:00.000 A 出库 0 50 250
4 2010-05-04 00:00:00.000 A 入库 50 0 300
5 2010-05-05 00:00:00.000 A 出库 0 20 280
1 2010-05-11 00:00:00.000 B 期初结存 2000 0 2000
2 2010-05-12 00:00:00.000 B 入库 1000 0 3000
3 2010-05-13 00:00:00.000 B 出库 0 500 2500
4 2010-05-14 00:00:00.000 B 入库 500 0 3000
5 2010-05-15 00:00:00.000 B 出库 0 200 2800
*/
update tb set 结存=b.结存
from (select 产品名称,结存=sum(结存+入库-出库) from tb a
where a.日期<=tb.日期 group by 产品名称) b
where b.产品名称=tb.产品名称
declare @table table (日期 datetime,产品名称 varchar(1),单据类型 varchar(8),数量 int)
insert into @table
select '2010-05-01','A','期初结存',200 union all
select '2010-05-02','A','入库',100 union all
select '2010-05-03','A','出库',50 union all
select '2010-05-04','A','入库',50 union all
select '2010-05-05','A','出库',20
;with maco as(
select a.日期,a.产品名称,a.单据类型,a.入库,
a.出库,isnull(a.结存,isnull(入库,0)-isnull(出库,0)) as 结存 from (
select 日期=convert(varchar(10),日期,120),产品名称,单据类型,
入库 =case 单据类型 when '入库' then 数量 else null end,
出库=case 单据类型 when '出库' then 数量 else null end,
结存=case 单据类型 when '期初结存' then 数量 else null end from @table
)a
)
select 日期,产品名称,单据类型,入库,出库,
(select sum(结存) from maco where 日期<=t.日期) as 结存 from maco t
/*
日期 产品名称 单据类型 入库 出库 结存
---------- ---- -------- ----------- ----------- -----------
2010-05-01 A 期初结存 NULL NULL 200
2010-05-02 A 入库 100 NULL 300
2010-05-03 A 出库 NULL 50 250
2010-05-04 A 入库 50 NULL 300
2010-05-05 A 出库 NULL 20 280
*/