-- 增加一個製令,一個品號以及兩張採購單.
-- 比較貼近實際情況
-- 使用 MySQL 8
select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+

create table manorder (
  ordid char(3) not null
, itemno char(5) not null
, reqamut int unsigned not null
);

insert into manorder values
('A01', 'book', 200),
('C02', 'desk', 120);

create table stock (
  itemno char(5) not null
, inventory int unsigned not null
);

insert into stock values
('book', 50),
('desk', 40);

create table purchase (
  itemno char(5) not null
, prchno char(4) not null
, qty int unsigned not null
);

insert into purchase values
('book', '0001', 50),
('book', '0002', 60),
('book', '0003', 80),
('desk', '1002', 50),
('desk', '1003', 70);

--

select ordid
     , itemno
     , reqamut
     , inventory
     , prchno
     , qty
     , sum(qty) over(partition by ordid, itemno order by prchno) as 'cumqty'
     , inventory + sum(qty) over(partition by ordid, itemno order by prchno) - reqamut as '可用量'
  from manorder m
  join stock s
 using (itemno)
  join purchase p
 using (itemno)
 ;
+-------+--------+---------+-----------+--------+-----+--------+-----------+
| ordid | itemno | reqamut | inventory | prchno | qty | cumqty | 可用量    |
+-------+--------+---------+-----------+--------+-----+--------+-----------+
| A01   | book   |     200 |        50 | 0001   |  50 |     50 |      -100 |
| A01   | book   |     200 |        50 | 0002   |  60 |    110 |       -40 |
| A01   | book   |     200 |        50 | 0003   |  80 |    190 |        40 |
| C02   | desk   |     120 |        40 | 1002   |  50 |     50 |       -30 |
| C02   | desk   |     120 |        40 | 1003   |  70 |    120 |        40 |
+-------+--------+---------+-----------+--------+-----+--------+-----------+
5 rows in set (0.03 sec)

MSSQL 2017
不同寫法，偷學一下屠豬大大也用兩種

SELECT O.[OrderId] '制令', P.[QNo] '品號',O.[NEEDQTY] '需用量',S.[QTY] '庫存量',P.[PurchaseId] '採購單',P.QTY '採購數量',sum(P1.QTY)+S.[QTY]-O.[NEEDQTY] '可用量' FROM  [Order] O 
LEFT JOIN [dbo].[Stock] S ON O.[QNo]=S.[QNo]
LEFT JOIN [dbo].[Purchase] P ON O.[QNo]=P.[QNo]
LEFT JOIN [dbo].[Purchase] P1 ON O.[QNo]=P1.[QNo]
WHERE   P.[PurchaseId] >= P1.[PurchaseId]
GROUP BY  O.[OrderId],P.[QNo],O.[NEEDQTY],P.[PurchaseId],S.[QTY],P.QTY
ORDER BY  O.[OrderId],P.[QNo],P.[PurchaseId]

SQL結果看這裡