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 行)
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 行)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货