https://ithelp.ithome.com.tw/questions/10207431
目前已經順利找出"可組成之數量"
但最終結果希望結合出貨單,每個品項後面都有可組成數量
也就是有沒有辦法一次計算多個品項之可組成數量

sp 資料表 "出貨單號","品項","要出數量"

CREATE TABLE sp(
	noa nvarchar(20),
	pno nvarchar(20),
    mount int
	
);

INSERT INTO sp (noa,pno,mount)
VALUES ('SP001','1',10),('SP001','2',20),('SP001','3',30)
--希望呈現以下
--查詢出"單號","品項","要出數量","可組成之數量","實際庫存"
select a.*,dbo.vr_stock(pno) vr_stock,b.stock
from sp a
left join pro b on a.pno = b.id

原先測試資料

CREATE TABLE relate(
	id varchar(5),
	parentid varchar(5),
	mount int
);

CREATE TABLE pro(
	id varchar(5),
	stock int
);

INSERT INTO relate (id,parentid,mount)
VALUES ('1-1', '1',1),('1-2', '1',1),('1-3', '1',1);

INSERT INTO pro (id,stock)
VALUES ('1',300),('2',300),('3',300)
,('1-1',1500),('1-2',11793),('1-3',1934);

with t1 as (
select a.Id,a.ParentId,a.mount,b.stock, floor(b.stock  / a.mount) as "prod"
  from relate a 
  left join pro b on a.Id = b.id
 where a.parentid = '1'
)
select min(prod)
  from t1;