mysql用elt和interval来分段查询计算快递收件费

数据导入

CREATE TABLE `test` (`id` varchar(10) NOT NULL,`件数` int(10) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT into test 
(id,件数)
VALUES
(1,99),
(2,150),
(3,201),
(4,260),
(5,301),
(6,350);

题目条件

假设一个快递员 收100件及以下 是按1元一件算钱, 101 到 200件 按 0.9元每件算, 依次类推…

那么问题来了, 当我收101件的时候 只能拿到90.9元 还不如 收100件 钱多.

所以老板的算法是这样的:

0 < X <= 100

1元/件

(这个区间最大收益是1*100 = 100元)

100 < X <= 200

如果X * 0.9 小于上个区间的最大值(100), 那么按 100 算.

如果X * 0.9 大于上个区间的最大值(100), 那么按 X*0.9 算.

(这个区间最大收益是0.9*200 = 180元)

200 < X <= 300

如果X * 0.8 小于上个区间的最大值(180), 那么按 180 算.

如果X * 0.8 大于上个区间的最大值(180), 那么按 X * 0.8 算.

(这个区间最大收益是0.8*300=240元)

300 < X

如果X * 0.7 小于上个区间的最大值(240), 那么按 240 算.

如果X * 0.7 大于上个区间的最大值(240), 那么按 X * 0.7 算.

通过件数 求收入

1. 用case when:

select 件数,
case when 件数 <= 100 then 件数*1
when 件数 >100 and 件数 <=200 and (件数*0.9) < 100 then 100
when 件数 >100 and 件数 <=200 and (件数*0.9) > 100 then 件数*0.9
when 件数 >200 and 件数 <=300 and (件数*0.8) < 180 then 180
when 件数 >200 and 件数 <=300 and (件数*0.8) > 180 then 件数*0.8
when 件数 >300 and (件数*0.7)  < 240 then 240
when 件数 >300 and (件数*0.7)  > 240 then 件数*0.7
end as 收益
from test

这个方法就完全是按照上面的题目来一步一步设置条件

2. 用elt和interval

select 件数, elt(interval(件数, 100, 112, 200, 226, 300, 343)+1, 1*件数, 100, 0.9*件数, 180, 0.8*件数, 240, 0.7*件数) as 收益
from test

首先介绍一下这两个函数

elt (N,str1,str2,str3,…)
如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。

mysql> select ELT(1, ‘a’, ‘b’, ‘c’, ‘d’);

    -> 'a'

mysql> select ELT(4, ‘a’, ‘b’, ‘c’, ‘d’);

    -> 'd'

interval（N,n1,n2,n3,…）
小于后面的某个参数，就返回这个参数的前一个位置数字

如果N < n1返回0，如果n1 <= N < n2返回1，如果n2 < = N < n3返回2 。如果N为NULL，它将返回-1。列表值必须是n1 < n2 < n3的升序形式。

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); （23小于30，30的位置是4，于是返回3）

    -> 3

mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);

    -> 2

mysql> SELECT INTERVAL(22, 23, 30, 44, 200);

    -> 0

回到上面的题:

select 件数, 
elt(interval(件数, 100, 112, 200, 226, 300, 343)+1, 1*件数, 100, 0.9*件数, 180, 0.8*件数, 240, 0.7*件数) as 收益
from test

首先我们计算出 几个临界值, 112, 226, 343, 大于等于这个值, 乘以对应比例就不会比上个区间的最大值小了.

通过elt和interval的结合. 将数据分成
[0-99] , [100-111] , [112-199] , [200-225] , [225-299] , [300-342] , [343以上] 七个段位,

然后 为每个段位匹配一个计算方式.

[1 * 件数] , [100] , [0.9 * 件数 ] , [180] , [ 0.8 * 件数 ] , [240] , [0.7 * 件数]

例如, 当件数等于101时interval()+1, 返回的值是2, 这个只值给elt()函数 得到的结果是第2个计算方式, 是100.

例如, 当件数等于226时interval()+1, 返回的值是5, 这个值传给elt()函数 得到的结果是第5个计算方式, 是0.8*件数.

最终结果: