declare @B table([F1] varchar(1),[F2] int) insert @B select 'A',190 insert @B select 'B',20 insert @B select 'B',40DECLARE @a table(F1 varchar(1), F2 INT, F3 INT, F4 INT) insert @a select 'A', 1, 45, 0 union all select 'A', 2, 105, 0 union all select 'A', 3, 205, 0 union all select 'B', 1, 50, 0 union all select 'B', 2, 60, 0 SELECT aa.f1,aa.f2,aa.f3,f4=CASE WHEN y-x>f3 THEN f3 ELSE (y-x) END FROM ( SELECT *,(SELECT isnull(sum(f3),0) FROM @a WHERE f1=a.f1 AND f2<a.f2)x,(SELECT sum(f2) FROM @b WHERE f1=a.f1 ) y FROM @a a )aa--result /*f1 f2 f3 f4 ---- ----------- ----------- ----------- A 1 45 45 A 2 105 105 A 3 205 40 B 1 50 50 B 2 60 10(所影响的行数为 5 行)*/
insert @B select 'A',190
insert @B select 'B',20
insert @B select 'B',40DECLARE @a table(F1 varchar(1), F2 INT, F3 INT, F4 INT)
insert @a select 'A', 1, 45, 0
union all select 'A', 2, 105, 0
union all select 'A', 3, 205, 0
union all select 'B', 1, 50, 0
union all select 'B', 2, 60, 0
SELECT aa.f1,aa.f2,aa.f3,f4=CASE WHEN y-x>f3 THEN f3 ELSE (y-x) END FROM
(
SELECT *,(SELECT isnull(sum(f3),0) FROM @a WHERE f1=a.f1 AND f2<a.f2)x,(SELECT sum(f2) FROM @b WHERE f1=a.f1 ) y FROM
@a a
)aa--result
/*f1 f2 f3 f4
---- ----------- ----------- -----------
A 1 45 45
A 2 105 105
A 3 205 40
B 1 50 50
B 2 60 10(所影响的行数为 5 行)*/