34,594
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([item_code] VARCHAR(3),[trx_price] INT,[adj_price] INT,[qty] INT)
INSERT [tb]
SELECT '001',20,0,10 UNION ALL
SELECT '002',30,0,58 UNION ALL
SELECT '003',89,0,56 UNION ALL
SELECT '004',23,0,17
--------------开始查询--------------------------
DECLARE @a INT =55
UPDATE TOP (1) t SET [adj_price] = @a-(SELECT SUM([trx_price]) FROM [tb] WHERE [item_code]<t.[item_code])
FROM [tb] AS t
WHERE @a<=(SELECT SUM([trx_price]) FROM [tb] WHERE [item_code]<=t.[item_code])
SELECT * FROM [tb]
----------------结果----------------------------
/*
item_code trx_price adj_price qty
001 20 0 10
002 30 0 58
003 89 5 56
004 23 0 17
*/