这是有问题的代码:create table test(a int); insert into test value(-100); insert into test value(-10); insert into test value(0); insert into test value(10); insert into test value(100);set @a0 := (select min(a) from test); set @a1 := (select max(a) from test);set @B0 := 5; set @B1 := 25;select (@B0 + (a - @a0) / (@a1 - @a0) * (@B1 - @B0)) as B from test;其中没有考虑 @a1-@a0 = 0 的情况, 并且没有做到截断. 如果手工设置a0 = 0, 则a=-100, -10的时候,结果应该为0, 但上面代码没有达到要求.
select case a when a < a0 then A0 when a > a1 then A1 else A0 + (a / (a1-a0)) * (A1-A0) end as a from test
select (@B0 + (a - @a0) / IF(@a1 - @a0=0,1,@a1 - @a0) * (@B1 - @B0)) as B from test;如果手工设置a0 = 0, 则a=-100, -10的时候,结果应该为0, 怎么计算的
if( a < a0) A = A0; else if( a > a1) A = A1; else //( a <= a0 && a >= a1) A = A0 + (a / (a1-a0)) * (A1-A0);SELECT CASE WHEN a < a0 THEN A0 WHEN a > a1 THEN A1 ELSE A0 + (a /IF(a1-a0=0,1,a1-a0)) * IF(A1-A0=0,1,a1-a0) END AS a FROM test;
"如果手工设置a0 = 0, 则a=-100, -10的时候,结果应该为0"大约意思就是将[a0, a1]区间里面的数映射到[A0,A1]区间, 但表中的数a不全在[a0,a1]区间内.当表中a值不在[a0, a1]内的时候,就按上限或者下限截断处理. a := 表中的值 if( a < a0) A = A0; else if( a > a1) A = A1; else //( a <= a0 && a >= a1) A = A0 + ((a - a0) / (a1-a0)) * (A1-A0);3楼的代码几乎可以达到要求了,希望还有更简洁美观的写法.
似乎没有 select case when GREATEST(a,(select min(a) from test),(select max(a) from test))=a then (select max(a) from test) when least(a,(select min(a) from test),(select max(a) from test))=a then (select min(a) from test) else ... end form tt
insert into test value(-100);
insert into test value(-10);
insert into test value(0);
insert into test value(10);
insert into test value(100);set @a0 := (select min(a) from test);
set @a1 := (select max(a) from test);set @B0 := 5;
set @B1 := 25;select (@B0 + (a - @a0) / (@a1 - @a0) * (@B1 - @B0)) as B from test;其中没有考虑 @a1-@a0 = 0 的情况, 并且没有做到截断.
如果手工设置a0 = 0, 则a=-100, -10的时候,结果应该为0, 但上面代码没有达到要求.
select case a when a < a0 then A0 when a > a1 then A1 else A0 + (a / (a1-a0)) * (A1-A0) end as a from test
怎么计算的
A = A0;
else if( a > a1)
A = A1;
else //( a <= a0 && a >= a1)
A = A0 + (a / (a1-a0)) * (A1-A0);SELECT CASE
WHEN a < a0 THEN A0
WHEN a > a1 THEN A1
ELSE A0 + (a /IF(a1-a0=0,1,a1-a0)) * IF(A1-A0=0,1,a1-a0) END AS a FROM test;
但表中的数a不全在[a0,a1]区间内.当表中a值不在[a0, a1]内的时候,就按上限或者下限截断处理.
a := 表中的值
if( a < a0)
A = A0;
else if( a > a1)
A = A1;
else //( a <= a0 && a >= a1)
A = A0 + ((a - a0) / (a1-a0)) * (A1-A0);3楼的代码几乎可以达到要求了,希望还有更简洁美观的写法.
select case when GREATEST(a,(select min(a) from test),(select max(a) from test))=a then (select max(a) from test)
when least(a,(select min(a) from test),(select max(a) from test))=a then (select min(a) from test)
else
...
end form tt