求一组先进先出的减库存的 SQL 语句在线等

2011-05-30 01:30:37 515 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL Server 用触发器实现库存的先进先出（有退货）限制： 1)进仓表、出仓表只能一次插入一条，多条触发器没有对应。 2)退货没有指定批次，是按最近原则自动匹配的。阅读详情

要求: 一个库存表一个业务表
每次减库存时按照自增列的顺序减库存
但是 BH 为编号 KH 为库房号
两个库号里的商品不能互相减库存
目的就是实现先进先出减库存法







create table #sp

(bh char(8) null default '',

 kh char(2) null default '',

 plh char(10) null default '',

 shl int,

 snn int IDENTITY (1, 1) NOT NULL 

)



create table #ls

(bh char(8) null default '',

 kh char(2) null default '',

 shl int

)



insert into #sp

(bh,kh,plh,shl)

select '100001','1','10111',20 

union

select '100001','1','10132',10 

union

select '100001','1','10133',20 

union

select '100001','1','10134',20 

union

select '100001','2','10111',12

union

select '100001','2','10132',1

union

select '100001','2','10234',230

union

select '100001','2','10235',25

union  

select '100002','1','10111',20 

union

select '100002','1','10132',10 

union

select '100002','1','10133',20 

union

select '100002','1','10134',20 

union

select '100002','2','10111',12

union

select '100002','2','10132',1

union

select '100002','2','10234',230

union

select '100002','2','10235',25



insert into #ls

(bh,kh,shl)

select '100001','1',50

union 

select '100001','2',40

union 

select '100002','1',30

union 

select '100002','2',10



select * from #sp

select * from #ls

...全文

按需求先进先出分配库存 --测试数据 create table #库存( wareid varchar(13), makeno varchar(40), storeqty numeric(16,6)) insert into #库存 select 'A','批号1',30 insert into #库存 select 'A','批号2',40 insert into #库存 select 'A','批号阅读详情

crazy_boom

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

crazy_boom 2011-06-26

打赏
举报

create table sp
(bh char(8) null default '',
kh char(2) null default '',
plh char(10) null default '',
shl int,
snn int IDENTITY (1, 1) NOT NULL
)

create table ls
(bh char(8) null default '',
kh char(2) null default '',
shl int
)

insert into sp
(bh,kh,plh,shl)
select '100001','1','10111',0
union
select '100001','1','10132',30
union
select '100001','1','10133',0
union
select '100001','1','10134',0
union
select '100001','2','10111',12
union
select '100001','2','10132',1
union
select '100001','2','10234',230
union
select '100001','2','10235',25
union
select '100002','1','10111',20
union
select '100002','1','10132',10
union
select '100002','1','10133',20
union
select '100002','1','10134',20
union
select '100002','2','10111',12
union
select '100002','2','10132',1
union
select '100002','2','10234',230
union
select '100002','2','10235',25

insert into ls
(bh,kh,shl)
select '100001','1',40
union
select '100001','2',40
union
select '100002','1',30
union
select '100002','2',10

select b.bh,b.kh,b.plh,sl=case when b.kcsl-ls.shl<=0 and b.kcsl<b.zsl then 0
when b.kcsl-ls.shl<=0 and b.kcsl=b.zsl then b.kcsl-ls.shl
when b.kcsl-ls.shl>0 and
(select isnull(sum(shl),0) from sp
where sp.bh=b.bh and sp.kh=b.kh and sp.snn<b.snn)<ls.shl
then b.kcsl-ls.shl else b.shl end,b.snn
from
(select *,kcsl=(select sum(shl) from sp a
where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn),
zsl=(select sum(shl) from sp a
where a.bh=sp.bh and a.kh=sp.kh) from sp) b
left join ls
on ls.bh=b.bh and ls.kh=b.kh

执行结果显示 SNN 为2、3、4 条记录的都为 -10 这样就多减了20个

crazy_boom 2011-06-18

打赏
举报

[Quote=引用 20 楼 cd731107 的回复:]
SQL code
--这个应该可以
select b.bh,b.kh,b.plh,sl=case when b.kcsl-ls.shl<=0 and b.kcsl<b.zsl then 0
when b.kcsl-ls.shl<=0 and b.kcsl=b.zsl then b.kcsl-ls.shl
when b.kcsl-ls.shl>0 and
(select isnull(s……
[/Quote]
这条语句是我想要的结果可是有个情况没有考虑到就是当出现当日某商品没有销售只有销售退货（或者退货的数量比销售数量大）即销售数量为负数的情况注意 LS 表不是销售明细表而是一天的商品的销售汇总
要求销售量为正数的时候按照入库序列进行减库存销售量为负数的时候按照有库存的商品的入库序列进行加库存
（同时要考虑该商品库存为0 为负数的情况）
当LS表为
insert into #ls
(bh,kh,shl)
select '100001','1',-30
union
select '100001','2',40
union
select '100002','1',30
union
select '100002','2',10

crazy_boom 2011-06-05

打赏
举报





create table #sp

(bh char(8) null default '',

 kh char(2) null default '',

 plh char(10) null default '',

 shl int,

 snn int IDENTITY (1, 1) NOT NULL 

)



create table #ls

(bh char(8) null default '',

 kh char(2) null default '',

 shl int

)



insert into #sp

(bh,kh,plh,shl)

select '100001','1','10111',20 

union

select '100001','1','10132',10 

union

select '100001','1','10133',20 

union

select '100001','1','10134',20 

union

select '100001','2','10111',12

union

select '100001','2','10132',1

union

select '100001','2','10234',230

union

select '100001','2','10235',25

union  

select '100002','1','10111',20 

union

select '100002','1','10132',10 

union

select '100002','1','10133',20 

union

select '100002','1','10134',20 

union

select '100002','2','10111',12

union

select '100002','2','10132',1

union

select '100002','2','10234',230

union

select '100002','2','10235',25



insert into #ls

(bh,kh,shl)

select '100001','1',90

union 

select '100001','2',40

union 

select '100002','1',30

union 

select '100002','2',10

---想要的结果为

-----------------------------------------------

100001  	1 	10111     	0	1

100001  	1 	10132     	0	2

100001  	1 	10133     	0	3

100001  	1 	10134          -20	4

100001  	2 	10111     	0	5

100001  	2 	10132     	0	6

100001  	2 	10234     	203	7

100001  	2 	10235     	25	8

100002  	1 	10111     	0	9

100002  	1 	10132     	0	10

100002  	1 	10133     	20	11

100002  	1 	10134     	20	12

100002  	2 	10111     	2	13

100002  	2 	10132     	1	14

100002  	2 	10234     	230	15

100002  	2 	10235     	25	16

crazy_boom 2011-06-05

打赏
举报

[Quote=引用 10 楼 hlf1989 的回复:]



--因为查询中有null值未判断，现在修改为下面

select b.bh,b.kh,b.plh,sl=case when b.kcsl-ls.shl<=0 then 0 

when b.kcsl-ls.shl>0 and  

(select isnull(sum(shl),0) from sp 

where sp.bh=b.bh and sp.kh=b.kh and sp.snn<b.snn)<ls.shl

then b.kcsl-ls.shl else b.shl end,b.snn

from  

(select *,kcsl=(select sum(shl) from sp a 

where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn) from sp) b

left join ls

on ls.bh=b.bh and ls.kh=b.kh

[/Quote]

这里有个问题如果销售数量大于库存数量那么这时库存就会减失败。例如：
insert into #ls
(bh,kh,shl)
select '100001','1',90
union
select '100001','2',40
union
select '100002','1',30
union
select '100002','2',10
我把第一行的select '100001','1',50 的数量改成90 这时库存就会发生错误少减20个

htl258_Tony 2011-06-05

打赏
举报

[Quote=引用 5 楼 crazy_boom 的回复:]
想得到的结果为：
100001 1 10111 0 1
100001 1 10132 0 2
100001 1 10133 0 3
100001 1 10134 20 4
100001 2 10111 0 5
100001 2 10132 0 6
100001 2 10234 203 7
100001 2 10235 25 8
100002 1 10111 0 9
100002……
[/Quote]

--> 生成测试数据表: [sp]

IF OBJECT_ID('[sp]') IS NOT NULL

	DROP TABLE [sp]

GO

CREATE TABLE [sp] ([bh] [int],[kh] [int],[plh] [int],[shl] [int],[snn] [int])

INSERT INTO [sp]

SELECT '100001','1','10111','20','1' UNION ALL

SELECT '100001','1','10132','10','2' UNION ALL

SELECT '100001','1','10133','20','3' UNION ALL

SELECT '100001','1','10134','20','4' UNION ALL

SELECT '100001','2','10111','12','5' UNION ALL

SELECT '100001','2','10132','1','6' UNION ALL

SELECT '100001','2','10234','230','7' UNION ALL

SELECT '100001','2','10235','25','8' UNION ALL

SELECT '100002','1','10111','20','9' UNION ALL

SELECT '100002','1','10132','10','10' UNION ALL

SELECT '100002','1','10133','20','11' UNION ALL

SELECT '100002','1','10134','20','12' UNION ALL

SELECT '100002','2','10111','12','13' UNION ALL

SELECT '100002','2','10132','1','14' UNION ALL

SELECT '100002','2','10234','230','15' UNION ALL

SELECT '100002','2','10235','25','16'



--> 生成测试数据表: [ls]

IF OBJECT_ID('[ls]') IS NOT NULL

	DROP TABLE [ls]

GO

CREATE TABLE [ls] ([bh] [int],[kh] [int],[shl] [int])

INSERT INTO [ls]

SELECT '100001','1','50' UNION ALL

SELECT '100001','2','40' UNION ALL

SELECT '100002','1','30' UNION ALL

SELECT '100002','2','10'



--SELECT * FROM [sp]

--SELECT * FROM [ls]



-->SQL查询如下:

;WITH t AS 

(

    SELECT rn = ROW_NUMBER()OVER(ORDER BY bh, kh, [snn]),*

    FROM   sp

),t1 AS

(

    SELECT *,

           tmpsum = (

               SELECT SUM(shl)

               FROM   t

               WHERE  bh = a.bh

                      AND kh = a.kh

                      AND rn <= a.rn

           )

    FROM   t a

)

UPDATE a

SET    a.shl = a.shl -CASE 

                           WHEN a.shl -(a.tmpsum -b.shl) <= 0 THEN 0

                           WHEN a.tmpsum -b.shl <= 0 THEN a.shl

                           ELSE a.shl -(a.tmpsum -b.shl)

                      END

FROM   t1 a

	JOIN (SELECT bh,kh,SUM(shl) shl

          FROM   ls

          GROUP BY bh,kh

         ) b

        ON  a.bh = b.bh

            AND a.kh = b.kh



SELECT *  FROM   sp

/*

bh          kh          plh         shl         snn

----------- ----------- ----------- ----------- -----------

100001      1           10111       0           1

100001      1           10132       0           2

100001      1           10133       0           3

100001      1           10134       20          4

100001      2           10111       0           5

100001      2           10132       0           6

100001      2           10234       203         7

100001      2           10235       25          8

100002      1           10111       0           9

100002      1           10132       0           10

100002      1           10133       20          11

100002      1           10134       20          12

100002      2           10111       2           13

100002      2           10132       1           14

100002      2           10234       230         15

100002      2           10235       25          16



(16 行受影响)

*/

crazy_boom 2011-06-05

打赏
举报

hlf1989 能否帮我写出来对应的update 语句更新库存呢
如果我的库存表有10万条库存记录 update 如何写效率更高呢

cd731107 2011-06-05

打赏
举报

[Quote=引用 13 楼 crazy_boom 的回复:]
不是流水账是按照库存的入库序列进行减库存
[/Quote]
10楼后来的做法还不对吗？

crazy_boom 2011-06-05

打赏
举报

不是流水账是按照库存的入库序列进行减库存

cd731107 2011-06-05

打赏
举报

--这个应该可以

select b.bh,b.kh,b.plh,sl=case when b.kcsl-ls.shl<=0 and b.kcsl<b.zsl then 0 

when b.kcsl-ls.shl<=0 and b.kcsl=b.zsl then  b.kcsl-ls.shl

when b.kcsl-ls.shl>0 and  

(select isnull(sum(shl),0) from sp 

where sp.bh=b.bh and sp.kh=b.kh and sp.snn<b.snn)<ls.shl

then b.kcsl-ls.shl else b.shl end,b.snn

from  

(select *,kcsl=(select sum(shl) from sp a 

where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn),

zsl=(select sum(shl) from sp a 

where a.bh=sp.bh and a.kh=sp.kh) from sp) b

left join ls

on ls.bh=b.bh and ls.kh=b.kh

crazy_boom 2011-06-05

打赏
举报

如果出现销售数量大于库存数量如何处理呢？

rucypli 2011-05-30

打赏
举报

没有时间字段吗

--小F-- 2011-05-30

打赏
举报

需要得到的结果是什么?

hlf1989 2011-05-30

打赏
举报

--猜一猜,忽略plh字段

select b.*,即时库存数量=ls.shl-b.kcsl from  

(select *,kcsl=(select sum(shl) from sp a where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn) from sp) b

left join ls

on ls.bh=b.bh and ls.kh=b.kh

hlf1989 2011-05-30

打赏
举报

plh此字段是神马？
要求的结果集也显示一下

中国风 2011-05-30

打赏
举报

樓主看看是否為以上顯示格式

庫存為扣減完時顯示為0，未減時為庫存數量

樓主定義為庫存？
這應該為進貨的流水賬

中国风 2011-05-30

打赏
举报

create table #sp

(bh char(8) null default '',

 kh char(2) null default '',

 plh char(10) null default '',

 shl int,

 snn int IDENTITY (1, 1) NOT NULL 

)



create table #ls

(bh char(8) null default '',

 kh char(2) null default '',

 shl int

)



insert into #sp

(bh,kh,plh,shl)

select '100001','1','10111',20 

union

select '100001','1','10132',10 

union

select '100001','1','10133',20 

union

select '100001','1','10134',20 

union

select '100001','2','10111',12

union

select '100001','2','10132',1

union

select '100001','2','10234',230

union

select '100001','2','10235',25

union  

select '100002','1','10111',20 

union

select '100002','1','10132',10 

union

select '100002','1','10133',20 

union

select '100002','1','10134',20 

union

select '100002','2','10111',12

union

select '100002','2','10132',1

union

select '100002','2','10234',230

union

select '100002','2','10235',25



insert into #ls

(bh,kh,shl)

select '100001','1',50

union 

select '100001','2',40

union 

select '100002','1',30

union 

select '100002','2',10



SELECT 

	a.snn,a.bh,a.kh,a.plh,a.shl,

	庫存=CASE WHEN b.sshl>=a.sshl THEN 0 WHEN b.sshl>a.sshl-a.shl THEN  b.sshl-(a.sshl-a.shl) ELSE a.shl END

FROM (select *,(SELECT SUM(shl) FROM #sp WHERE bh=a.bh AND kh=a.kh AND snn<=a.snn) AS sshl  from #sp AS a) AS a

	LEFT JOIN (SELECT bh,kh,SUM(shl) AS sshl FROM #ls GROUP BY bh,kh) AS b  ON a.bh=b.bh AND a.kh=b.kh

/*

snn	bh	kh	plh	shl	庫存

1	100001  	1 	10111     	20	0

2	100001  	1 	10132     	10	0

3	100001  	1 	10133     	20	0

4	100001  	1 	10134     	20	20

5	100001  	2 	10111     	12	0

6	100001  	2 	10132     	1	0

7	100001  	2 	10234     	230	27

8	100001  	2 	10235     	25	25

9	100002  	1 	10111     	20	0

10	100002  	1 	10132     	10	0

11	100002  	1 	10133     	20	20

12	100002  	1 	10134     	20	20

13	100002  	2 	10111     	12	10

14	100002  	2 	10132     	1	1

15	100002  	2 	10234     	230	230

16	100002  	2 	10235     	25	25

*/

hlf1989 2011-05-30

打赏
举报

[Quote=引用 7 楼 crazy_boom 的回复:]
引用 6 楼 hlf1989 的回复:

使用这个语句得出的结果又点补大正确
第一 select sum(kcsl) from #sp 总库存为 676
select sum(shl) from #ls 总数量为 130
但是最后求出的结果的 SUM(SL) 为 556 少减了10 个经对比发现
SNN=13 那一行没有被减库存库存应该为2个才对
[/Quote]

--因为查询中有null值未判断，现在修改为下面

select b.bh,b.kh,b.plh,sl=case when b.kcsl-ls.shl<=0 then 0 

when b.kcsl-ls.shl>0 and  

(select isnull(sum(shl),0) from sp 

where sp.bh=b.bh and sp.kh=b.kh and sp.snn<b.snn)<ls.shl

then b.kcsl-ls.shl else b.shl end,b.snn

from  

(select *,kcsl=(select sum(shl) from sp a 

where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn) from sp) b

left join ls

on ls.bh=b.bh and ls.kh=b.kh

快溜 2011-05-30

打赏
举报

游标写先进先出！

快溜 2011-05-30

打赏
举报

用游标。

crazy_boom 2011-05-30

打赏
举报

[Quote=引用 6 楼 hlf1989 的回复:]
[/Quote]

使用这个语句得出的结果又点补大正确
第一 select sum(kcsl) from #sp 总库存为 676
select sum(shl) from #ls 总数量为 130
但是最后求出的结果的 SUM(SL) 为 556 少减了10 个经对比发现
SNN=13 那一行没有被减库存库存应该为2个才对

加载更多回复（2）

求一组 先进先出的减库存的 SQL 语句 在线等

相关推荐

求一组先进先出的减库存的 SQL 语句在线等