sql 判定后自动插入

[appo_li]

需求说明，我有库存表A，同一产品在不同的仓库中有着不同的数量，现在要出库500，按照先出数量由小到大出货的逻辑，先出仓库1中的100，再出2中的200，再出3中的200，将这三个数据（100，200,200）插入到表B中，这个如果纯用sql写该怎么写？

表结构如下：

CREATE TABLE [表A](
[产品ID] [int] NULL,
[仓库ID] [int] NULL,
[库存数量] [float] NULL
)

create table 表B(
[产品ID] [int] NULL,
[仓库ID] [int] NULL,
[出库数量] [float] NULL
)

INSERT INTO 表A values(1.01,1,100)
INSERT INTO 表A values(1.01,2,200)
INSERT INTO 表A values(1.01,3,300)

declare @auxneed float
set @auxneed=500


憋半天，没想出来怎么搞，烦请高手指导一下。

[Ginnnnnnnn]

你应该还要考虑多次出库的情况哦

CREATE TABLE [表A](
[产品ID] [int] NULL,
[仓库ID] [int] NULL,
[库存数量] [float] NULL
)

create table 表B(
[产品ID] [int] NULL,
[仓库ID] [int] NULL,
[出库数量] [float] NULL
)

INSERT INTO 表A values(1.01,1,100)
INSERT INTO 表A values(1.01,2,200)
INSERT INTO 表A values(1.01,3,300)
INSERT INTO 表A values(1.01,4,400)

declare @auxneed float
set @auxneed=500
;WITH TA AS
(
	SELECT a.产品ID,a.仓库ID,a.库存数量 - b.出库数量 AS 库存数量
		FROM dbo.表A a
			CROSS APPLY(SELECT ISNULL(SUM(出库数量),0) AS 出库数量 FROM dbo.表B WHERE a.产品ID = 产品ID AND a.仓库ID = 仓库ID ) b
		WHERE a.库存数量 > b.出库数量
),CTE AS(
SELECT a.产品ID,a.仓库ID,MAX(a.库存数量) AS 库存数量,
		ISNULL(SUM(b.库存数量),0) AS 总库存		
		FROM TA a
		LEFT JOIN TA b ON a.产品ID = b.产品ID AND a.仓库ID > b.仓库ID
		GROUP BY a.产品ID,a.仓库ID)
INSERT INTO dbo.表B
        ( 产品ID, 仓库ID, 出库数量 )
SELECT CTE.产品ID,CTE.仓库ID,CASE WHEN @auxneed > CTE.总库存 + CTE.库存数量 THEN 库存数量 ELSE @auxneed - CTE.总库存 END
	FROM CTE 
		WHERE  总库存 < @auxneed

DROP TABLE dbo.表A
DROP TABLE dbo.表B

[唐诗三百首]

declare @pid int,@auxneed float

select @pid=1,   --> 严谨来讲,需指定产品ID
       @auxneed=500

;with t as(
select 仓库ID,库存数量,rn=row_number() over(order by 库存数量) 
 from 表A 
 where 产品ID=@pid and 库存数量>0),
u as(
select a.*,sq=(select sum(b.库存数量) from t b where b.rn<=a.rn)
  from t a),
v as(
select 仓库ID,
       qty=case when @auxneed>=sq then u.库存数量
                when @auxneed<sq and @auxneed>(sq-库存数量) then @auxneed-(sq-库存数量)
                else 0 end
 from u)
insert into 表B(产品ID,仓库ID,出库数量)
select @pid,
       仓库ID,
       qty
 from v
 where qty>0


-- 结果
select * from 表B

/*
产品ID        仓库ID        出库数量
----------- ----------- ----------------------
1           1           100
1           2           200
1           3           200

(3 row(s) affected)
*/

[appo_li]

引用 1楼唐诗三百首 的回复:

declare @pid int,@auxneed float

select @pid=1,   --> 严谨来讲,需指定产品ID
       @auxneed=500

;with t as(
select 仓库ID,库存数量,rn=row_number() over(order by 库存数量) 
 from 表A 
 where 产品ID=@pid and 库存数量>0),
u as(
select a.*,sq=(select sum(b.库存数量) from t b where b.rn<=a.rn)
  from t a),
v as(
select 仓库ID,
       qty=case when @auxneed>=sq then u.库存数量
                when @auxneed<sq and @auxneed>(sq-库存数量) then @auxneed-(sq-库存数量)
                else 0 end
 from u)
insert into 表B(产品ID,仓库ID,出库数量)
select @pid,
       仓库ID,
       qty
 from v
 where qty>0


-- 结果
select * from 表B

/*
产品ID        仓库ID        出库数量
----------- ----------- ----------------------
1           1           100
1           2           200
1           3           200

(3 row(s) affected)
*/

谢谢，正好解决问题

[appo_li]

引用 2楼Ginnnnnnnn 的回复:

你应该还要考虑多次出库的情况哦

CREATE TABLE [表A](
[产品ID] [int] NULL,
[仓库ID] [int] NULL,
[库存数量] [float] NULL
)

create table 表B(
[产品ID] [int] NULL,
[仓库ID] [int] NULL,
[出库数量] [float] NULL
)

INSERT INTO 表A values(1.01,1,100)
INSERT INTO 表A values(1.01,2,200)
INSERT INTO 表A values(1.01,3,300)
INSERT INTO 表A values(1.01,4,400)

declare @auxneed float
set @auxneed=500
;WITH TA AS
(
	SELECT a.产品ID,a.仓库ID,a.库存数量 - b.出库数量 AS 库存数量
		FROM dbo.表A a
			CROSS APPLY(SELECT ISNULL(SUM(出库数量),0) AS 出库数量 FROM dbo.表B WHERE a.产品ID = 产品ID AND a.仓库ID = 仓库ID ) b
		WHERE a.库存数量 > b.出库数量
),CTE AS(
SELECT a.产品ID,a.仓库ID,MAX(a.库存数量) AS 库存数量,
		ISNULL(SUM(b.库存数量),0) AS 总库存		
		FROM TA a
		LEFT JOIN TA b ON a.产品ID = b.产品ID AND a.仓库ID > b.仓库ID
		GROUP BY a.产品ID,a.仓库ID)
INSERT INTO dbo.表B
        ( 产品ID, 仓库ID, 出库数量 )
SELECT CTE.产品ID,CTE.仓库ID,CASE WHEN @auxneed > CTE.总库存 + CTE.库存数量 THEN 库存数量 ELSE @auxneed - CTE.总库存 END
	FROM CTE 
		WHERE  总库存 < @auxneed

DROP TABLE dbo.表A
DROP TABLE dbo.表B

谢谢，也能解决问题，因为我是类似的应用