--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([料号] VARCHAR(1),[跟踪号] INT,[需求数] INT,[库存数] INT)
INSERT [tb]
SELECT 'A',NULL,4000,2000 UNION ALL
SELECT 'A',111,4000,3000 UNION ALL
SELECT 'B',NULL,6000,2000 UNION ALL
SELECT 'B',333,6000,2000
GO

--> 测试语句：
; with t as
(
select *,
[调整数]= case when [需求数]>[库存数] then [库存数] else [库存数]-[需求数]end,
[余数]=[需求数]-(case when [需求数]>[库存数] then [库存数] else [库存数]-[需求数] end)
from [tb] where [跟踪号] is null
union all
select b.*,[调整数]=case when [余数]<b.[库存数] then [余数] else b.[库存数] end,
[余数]=(case when [余数]<b.[库存数] then [余数] else b.[库存数] end)-t.[余数]
from t,tb as b where b.[料号]=t.[料号] and b.[跟踪号] is not null and [余数]>0
)
select [料号],[跟踪号],[需求数],[库存数],[调整数]from t order by [料号]


/*

料号   跟踪号         需求数         库存数         调整数
---- ----------- ----------- ----------- -----------
A    NULL        4000        2000        2000
A    111         4000        3000        2000
B    NULL        6000        2000        2000
B    333         6000        2000        2000

(4 行受影响)


*/

------解决方案--------------------
另外，你楼上的不是兄台，如果资料没错的话ta是个女的。

------解决方案--------------------
SQL code

--> 测试数据：[tb]
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO
CREATE TABLE #tmp (物料 VARCHAR(50),跟踪号 VARCHAR(50),[需求数] INT,[库存数] INT)
INSERT #tmp
SELECT '01.01.02.M003EVAN',NULL,4000,2000 UNION ALL
SELECT '01.01.02.M003EVAN','预测001',4000,3000 UNION ALL
SELECT '01.01.04.MC051EVAN',NULL,4000,10000 UNION ALL
SELECT '01.03.06.P03018EVAN',NULL,4000,50000
GO

--> 测试语句：
with t as
(
select *,
[调整数]= case when [需求数]>[库存数] then [库存数] else [需求数]end,
[余数]=[需求数]-(case when [需求数]>[库存数] then [库存数] else [需求数] end)
from #tmp where [跟踪号] is null
union all
select b.*,[调整数]=case when [余数]<b.[库存数] then [余数] else b.[库存数] end,
[余数]=(case when [余数]<b.[库存数] then [余数] else b.[库存数] end)-t.[余数]
from t,#tmp as b where b.[物料]=t.[物料] and b.[跟踪号] is not null and [余数]>0
)
select [物料],[跟踪号],[需求数],[库存数],[调整数]from t order by [物料]