求一组 先进先出的减库存的 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
...全文
444 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)

22,301

社区成员

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

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