列和相减 L3-L2

最新推荐文章于 2025-04-15 22:01:20 发布

转载最新推荐文章于 2025-04-15 22:01:20 发布 · 68 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/385126

--列和相减 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，如需转载请自行联系原作者