示例薪资:
8k-15k
4k-6k
10k-18k
18k-25k
10k-20k
20k-40k
截取最低薪资:
select left(salary,locate ('k',salary)),salary
-- -- 亦可以写成locate('k',salary,3),后面的3表示从第三个位置开始找
from dataanalyst
结果:
8k
4k
10k
18k
10k
20k
截取最高薪资:
select right(salary,locate ('-',salary)-1)salary from dataanalyst
其中right 表示从右边开始截取,locate ('-',salary)表示定位'-'出现的位置
结果:
15k
6k
18k
25k
20k
40k
另:可以用length函数确定某个字符的长度,substr函数截取函数,这样也可以实现薪资值的提取
其中substr函数:substr(对哪个字符串进行截取,从哪开始,截取多长)
select
SUBSTR(salary,locate ('-',salary)+1,length(salary)-locate('-',salary)),salar
from dataanalyst
这里得到的结果是薪资上限值
拓展:
对薪资进行分组(以平均薪资为判决标准,每隔10k为一组,最高一组为30+)
select
case
when(bottom+top)/2 <= 10 then '0-10'
when(bottom+top)/2 <= 20 then '10-20'
when(bottom+top)/2 <= 30 then '20-30'
else '30+'
end,
salary from
(
select
salary,
left(salary,locate ('k',salary)-1) as 'bottom',
SUBSTR(salary,locate ('-',salary)+1,length(salary)-locate('-',salary)-1) as 'top'
from data.dataanalyst
)as t
结果:
组别 原薪资
10-20 8k-15k
0-10 4k-6k
10-20 10k-18k
20-30 18k-25k
10-20 10k-20k
20-30 20k-40k