22,210
社区成员
发帖
与我相关
我的任务
分享
--库存表有如此数量 如何按序列生成所需要的数量
Create table #tb
(bh char(2) null ,
xh int ,
sl int
)
insert into #tb
select '12',1,2 union
select '12',2,3 union
select '12',3,10 union
select '13',4,12 union
select '13',5,8 union
select '14',6,100 union
select '14',7,10
--编号12的数量要18个 编号13的要15个 编号14的要15个
--如何按序列提供可需要的数量
--想要的结果是
bh xh sl
12 1 2
12 2 3
12 3 10
13 4 12
13 5 3
14 6 15
Create table #tb
(bh char(2) null ,
xh int ,
sl int
)
insert into #tb
select '12',1,2 union
select '12',2,3 union
select '12',3,10 union
select '13',4,12 union
select '13',5,8 union
select '14',6,100 union
select '14',7,10
GO
DECLARE @bh CHAR(2)='13',@sl INT=15
;WITH tb
AS
(
SELECT *,(SELECT SUM(sl) FROM #tb WHERE bh=a.bh AND xh<=a.xh) AS SumSl FROM #tb AS a WHERE a.bh=@bh AND EXISTS (SELECT 1 FROM #tb WHERE bh=a.bh AND xh<a.xh HAVING ISNULL(SUM(sl),0)<@sl)
)
SELECT bh,xh,sl=CASE WHEN tb.SumSl<=@sl THEN sl ELSE @sl+sl-SumSl END FROM tb
/*
13 4 12
13 5 3
*/
IF OBJECT_ID('tempdb..#tb') IS NOT NULL DROP TABLE #tb
Create table #tb
( id INT IDENTITY(1,1),
bh char(2) null ,
xh int ,
sl int
)
insert into #tb
select '12',1,2 union
select '12',2,3 union
select '12',3,10 union
select '13',4,12 union
select '13',5,8 union
select '14',6,100 union
select '14',7,10
DECLARE @TotalQty INT=45
--所有的bh共用某个数量
SELECT @TotalQty- isnull(p.UsedQty,0) AS AvailabelQty, CASE WHEN @TotalQty- isnull(p.UsedQty,0)>sl THEN sl ELSE @TotalQty- isnull(p.UsedQty,0) END AS GetQty
,*
FROM #tb AS t
OUTER APPLY (SELECT SUM(sl) AS UsedQty FROM #tb WHERE id<t.id) AS p
WHERE @TotalQty- isnull(p.UsedQty,0)>0
----------- 每个bh有固定的数量------------
DECLARE @RequestQty INT=15
SELECT @RequestQty- isnull(p.UsedQty,0) AS AvailabelQty, CASE WHEN @RequestQty- isnull(p.UsedQty,0)>sl THEN sl ELSE @RequestQty- isnull(p.UsedQty,0) END AS GetQty
,*
FROM #tb AS t
OUTER APPLY (SELECT SUM(sl) AS UsedQty FROM #tb WHERE id<t.id AND bh=t.bh) AS p
WHERE @RequestQty- isnull(p.UsedQty,0)>0