mysql - 如何简化带有很多 “case when then else …” 的 sql

Question

遇到很多when的sql代码:

case
    when callDuration >    0 and callDuration <  30 then  1.4
    when callDuration >=  30 and callDuration <  60 then  2.3
    when callDuration >=  60 and callDuration < 120 then  3.7
    when callDuration >= 120 and callDuration < 180 then  4.5
    when callDuration >= 180 and callDuration < 240 then  5.2
    when callDuration >= 240 and callDuration < 300 then  6.1
    when callDuration >= 300 and callDuration < 360 then  7.3
    when callDuration >= 360 and callDuration < 420 then  8.4
    when callDuration >= 420 and callDuration < 480 then  9.2
    when callDuration >= 480 and callDuration < 540 then 10.1
    when callDuration >= 540 and callDuration < 600 then 11.9
    when callDuration >= 600                        then 12.3
end as duration

如果有100行这样的when和then，怎么简化更优雅，我可以考虑用Jinjia Template或者用lookup table。有没有更好的方法，不受特定变体的限制？

Answer 1

方法

我认为最优雅的解决方案是查找表(您在上面提到过)。

下面是一个例子，但为了简单起见，我没有输入你例子中​​列出的所有范围。

创建数据

create table lookupTable(
startCallDuration int,
endCallDuration int,
returnValue float);

insert into lookupTable values (0,30,1.4);
insert into lookupTable values (30,60,2.3);
insert into lookupTable values (60,120,3.7);
insert into lookupTable values (120,999999999,4.5);

create table callDuration(
callDuration int );

insert into callDuration values (30);
insert into callDuration values (60);

SQL语句

select returnValue from lookupTable l, callDuration c
where c.callDuration >= startCallDuration and
c.callDuration < endCallDuration;

Sql语句(内连接)

select returnValue from lookupTable l inner join callDuration c
on c.callDuration >= startCallDuration and
c.callDuration < endCallDuration;

SqlFiddle:http://www.sqlfiddle.com/#!9/11009e6/3