我把测试数据做了调整 你看一下 我写的有问题没
你可以看着改一下
--> 测试数据：[ta]
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([料号] VARCHAR(10),[是否替代料] VARCHAR(10),[原料号] VARCHAR(10))
INSERT [ta]
SELECT 'A','否','1' UNION ALL
SELECT 'A1','是','A' UNION ALL
SELECT 'A2','是','A' UNION ALL
SELECT 'B','否','1' UNION ALL
SELECT 'B1','是','B' UNION ALL
SELECT 'AA','否','1' UNION ALL
SELECT 'AA1','是','AA'
--> 测试数据：[呆料表]
IF OBJECT_ID('[呆料表]') IS NOT NULL DROP TABLE [呆料表]
GO
CREATE TABLE [呆料表]([料号] VARCHAR(10),[呆料数量] INT)
INSERT [呆料表]
SELECT 'A',100 UNION ALL
SELECT 'A1',5000 UNION ALL
SELECT 'A2',3000 UNION ALL
SELECT 'AA1',200 UNION ALL
SELECT 'B1',2000
--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([料号] VARCHAR(10),[需求数] INT)
INSERT [tb]
SELECT 'A',6500 UNION ALL
SELECT 'B',6500 UNION ALL
SELECT 'AA',6500
;WITH cte AS
(
SELECT a.[料号],a.[是否替代料],a.[料号] AS [原料号],[呆料数量]=ISNULL(b.[呆料数量],0) FROM [ta] AS a LEFT JOIN [呆料表] b ON a.[料号]=b.[料号]
WHERE a.[原料号]='1'
UNION ALL
SELECT a.[料号],a.[是否替代料],a.[原料号],[呆料数量]=ISNULL(b.[呆料数量],0) FROM [ta] AS a
INNER JOIN [呆料表] b ON a.[料号]=b.[料号]
INNER JOIN cte AS t ON t.[料号]=a.[原料号]
)
SELECT [料号],[需求数],[申购数]=CASE WHEN [呆料数量]>=[需求数] THEN 0 ELSE [需求数]-[呆料数量] END
FROM [tb] AS a,
(
SELECT [原料号],SUM([呆料数量]) AS [呆料数量] FROM cte GROUP BY [原料号]
) AS b
WHERE a.[料号]=b.[原料号]
