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

crazy_boom 2011-05-30 01:30:37
要求: 一个库存表 一个业务表
每次减库存时 按照 自增列的顺序减库存
但是 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
...全文
446 23 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
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）
库存管理系统
c#+sql2000开发的
酒店订房管理系统 ASP.NET+SQLServer2000
该程序为酒店订房管理系统 ASP.NET+SQLServer2000
三层商品进销存管理系统(VB+SQL)
三层结构的商品的进销存管理系统,使用VB+SQL,包含存储过程\触发器\等
仓储管理系统wms
用vc开发的仓储管理系统，是我的课程大作业，没有代码注释。连接有数据库。
数据库应用 餐饮管理系统
完整的餐饮管理系统 使用sql server2000作为后台开发工具，vc++作为前台接口 用用吧
疑难问题

22,300

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章