--列和相减 L3-L2
declare @test table(L1 varchar(50),L2 int,L3 int)
insert into @test
select 'A1',10,10 union all
select 'A1',10,5 union all
select 'A1',5,5 union all
select 'B1',10,20 union all
select 'B1',5,5
select * from @test
select a.*,b.L4 from @test a join
(select L1,sum(L3)-Sum(L2) as L4 from @test group by L1) b on a.L1=b.L1
/*
L1 L2 L3
-------------------------------------------------- ----------- -----------
A1 10 10
A1 10 5
A1 5 5
B1 10 20
B1 5 5
L1 L2 L3 L4
-------------------------------------------------- ----------- ----------- -----------
A1 10 10 -5
A1 10 5 -5
A1 5 5 -5
B1 10 20 10
B1 5 5 10
(5 行受影响)
*/
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638153.html，如需转载请自行联系原作者
