列和相减 L3-L2

--列和相减 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 行受影响)
*/
posted @ 2010-01-03 10:55 曾祥展 阅读(...) 评论(...) 编辑 收藏