set @sum=@h1+@h2+@h3+@h4+@h5+@h6+@h7+@h8+@h9+@h10+@h11+@h12+@h13+@h14+@h15+@h16+@h17+@h18+@h19+@h20+@h21+@h22+@h23+@24
set @avg=@sum/24
这样一条语句,想要改写成只当数据大于0时才参与计算,小于0时不参与计算,应该怎么写?
set @avg=@sum/24
这样一条语句,想要改写成只当数据大于0时才参与计算,小于0时不参与计算,应该怎么写?
if(@sum>0)
还是
if(@avg>0)
@sum=case when @h1>0 then @h1 else 0 end
+case when @h2>0 then @h2 else 0 end
+case when @h3>0 then @h3 else 0 end
+case when @h4>0 then @h4 else 0 end
+case when @h5>0 then @h5 else 0 end
.........set @avg=@sum/24
.....
+case when @h24>0 WHEN @h24 ELSE 0 END
set @avg=@sum/24
if(@sum>0)
set @avg=@sum/24
select col=@h1 union
select col=@h2 union
select col=@h3 union
select col=@h4 union
select col=@h5 union
select col=@h6 union
select col=@h7 union
select col=@h8 union
select col=@h9 union
select col=@h10 union
select col=@h11 union
select col=@h12 union
select col=@h13 union
select col=@h14 union
select col=@h15 union
select col=@h16 union
select col=@h17 union
select col=@h18 union
select col=@h19 union
select col=@h20 union
select col=@h21 union
select col=@h22 union
select col=@h23 union
select col=@h24 )t
where col1>0
if @sum<0
print '小于零,不能除以24'
else
set @avg=@sum/24
select @avg=avg(col1) from (
select col=@h1 union all
select col=@h2 union all
select col=@h3 union all
select col=@h4 union all
select col=@h5 union all
select col=@h6 union all
select col=@h7 union all
select col=@h8 union all
select col=@h9 union all
select col=@h10 union all
select col=@h11 union all
select col=@h12 union all
select col=@h13 union all
select col=@h14 union all
select col=@h15 union all
select col=@h16 union all
select col=@h17 union all
select col=@h18 union all
select col=@h19 union all
select col=@h20 union all
select col=@h21 union all
select col=@h22 union all
select col=@h23 union all
select col=@h24 )t
where col1>0
select col=1.0*@h1 union all
select col=@h2 union all
select col=@h3 union all
select col=@h4 union all
select col=@h5 union all