22,210
社区成员
发帖
与我相关
我的任务
分享
declare @t table (月份 varchar(10), 水价 dec(5,2),本月水量 int,本月水费 int)
insert @t select '200801', 1.00 , 5000 , 5000
insert @t select '200801', 2.00 , 1000 , 2000
insert @t select '200802', 1.00 , 5500 , 5500
insert @t select '200802', 2.00 , 1800 , 3600
insert @t select '200901', 1.00 , 6000 , 6000
insert @t select '200901', 2.00 , 8000 , 16000
insert @t select '200901', 2.50 , 1000 , 2500
select
水价=isnull(a.水价,b.水价),
水量同比=case when a.水价 is null or b.水价 is null then 0 else cast(cast(b.本月水量 as dec(10,2))/cast(a.本月水量 as dec(10,2)) as dec(3,1)) end,
水费同比=case when a.水价 is null or b.水价 is null then 0 else cast(cast(b.本月水费 as dec(10,2))/cast(a.本月水费 as dec(10,2)) as dec(3,1)) end
from
(select * from @t where 月份='200801') a
full join
(select * from @t where 月份='200901') b
on a.月份=cast(left(b.月份,4)-1 as varchar)+right(b.月份,2)
and a.水价=b.水价
水价 水量同比 水费同比
------- ----- -----
1.00 1.2 1.2
2.00 8.0 8.0
2.50 0 0
(所影响的行数为 3 行)