34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE a(ID INT,pro_id INT, pro_amount INT,pro_price money,pro_money money, pro_cost money,stock_sort INT)
INSERT a SELECT 1,1, 5, 10.00,50.00,10.00, 1
UNION ALL SELECT 1,1, 10,5.00, 50.00,5.00, 1
UNION ALL SELECT 1,1, 6, 15.00,NULL, NULL, 2
UNION ALL SELECT 1,1, 10,12.00,120.00,120.00,1
UNION ALL SELECT 1,1, 2, 20.00,NULL, NULL, 2 L
UNION ALL SELECT 1,2, 5, 16.00,80.00, 16.00, 1
UNION ALL SELECT 1,2, 2, 20.00,NULL, NULL, 2
DECLARE @a TABLE(xID INT IDENTITY(1,1),ID INT,pro_id INT, pro_amount INT,pro_price money,pro_money money, pro_cost money,stock_sort INT)
INSERT @a SELECT * FROM a
DECLARE @i INT
SET @i=0
WHILE @@ROWCOUNT>0
BEGIN
SET @i=@i+1
UPDATE a SET pro_cost=(SELECT sum(CASE WHEN stock_sort=1 THEN pro_money ELSE -pro_money END)*1.0/sum(CASE WHEN stock_sort=1 THEN pro_amount ELSE -pro_amount END ) FROM @a WHERE pro_id=a.pro_id AND xid<a.xid ) FROM @a a WHERE xid=@i AND pro_cost IS NULL
UPDATE @a SET pro_money=CASE WHEN pro_money IS NULL THEN pro_cost*pro_amount ELSE pro_money END WHERE xid=@i
END
SELECT * FROM @a
--result
/*
xID ID pro_id pro_amount pro_price pro_money pro_cost stock_sort
----------- ----------- ----------- ----------- --------------------- --------------------- --------------------- -----------
1 1 1 5 10.0000 50.0000 10.0000 1
2 1 1 10 5.0000 50.0000 5.0000 1
3 1 1 6 15.0000 40.0002 6.6667 2
4 1 1 10 12.0000 120.0000 120.0000 1
5 1 1 2 20.0000 18.9474 9.4737 2
6 1 2 5 16.0000 80.0000 16.0000 1
7 1 2 2 20.0000 32.0000 16.0000 2
(所影响的行数为 7 行)
*/