SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) 
	Jan 23 2020 21:00:04 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)




CREATE TABLE [dbo].[Order](
	[OrderId] [varchar](10) NULL,
	[QNo] [varchar](10) NULL,
	[NEEDQTY] [int] NULL
) ON [PRIMARY]



CREATE TABLE [dbo].[Purchase](
	[QNo] [varchar](10) NULL,
	[PurchaseId] [varchar](10) NULL,
	[QTY] [int] NULL
) ON [PRIMARY]



CREATE TABLE [dbo].[Stock](
	[QNo] [varchar](10) NULL,
	[QTY] [int] NULL
) ON [PRIMARY]

INSERT [dbo].[Order] ([OrderId], [QNo], [NEEDQTY]) VALUES (N'A01', N'book', 200)
INSERT [dbo].[Order] ([OrderId], [QNo], [NEEDQTY]) VALUES (N'A02', N'PEN', 100)

INSERT [dbo].[Purchase] ([QNo], [PurchaseId], [QTY]) VALUES (N'book', N'0001', 50)
INSERT [dbo].[Purchase] ([QNo], [PurchaseId], [QTY]) VALUES (N'book', N'0002', 60)
INSERT [dbo].[Purchase] ([QNo], [PurchaseId], [QTY]) VALUES (N'book', N'0003', 80)
INSERT [dbo].[Purchase] ([QNo], [PurchaseId], [QTY]) VALUES (N'PEN', N'P001', 30)
INSERT [dbo].[Purchase] ([QNo], [PurchaseId], [QTY]) VALUES (N'PEN', N'P002', 50)
INSERT [dbo].[Purchase] ([QNo], [PurchaseId], [QTY]) VALUES (N'PEN', N'P003', 90)

INSERT [dbo].[Stock] ([QNo], [QTY]) VALUES (N'book', 50)
INSERT [dbo].[Stock] ([QNo], [QTY]) VALUES (N'PEN', 10)

10 rows affected

	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]


制令
品號
需用量
庫存量
採購單
採購數量
可用量


A01
book
200
50
0001
50
-100

A01
book
200
50
0002
60
-40

A01
book
200
50
0003
80
40

A02
PEN
100
10
P001
30
-60

A02
PEN
100
10
P002
50
-10

A02
PEN
100
10
P003
90
80

制令	品號	需用量	庫存量	採購單	採購數量	可用量
A01	book	200	50	0001	50	-100
A01	book	200	50	0002	60	-40
A01	book	200	50	0003	80	40
A02	PEN	100	10	P001	30	-60
A02	PEN	100	10	P002	50	-10
A02	PEN	100	10	P003	90	80