SQL函数

1.left/locate，函数组合获取薪资下限

SELECT left(salary,locate('k',salary)-1),
salary FROM data.dataanalyst;

 

8	8k-15k
20	20k-40k
10	10k-18k
8	8k-16k
10	10k-18k
15	15k-30k
16	16k-25k
20	20k-35k
10	10k-20k
4	4k-6k

 

2.函数嵌套获取薪资上下限

SELECT 
left(salary,locate('k',salary)-1),
locate('-',salary),
length(salary),
left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1),
salary FROM data.dataanalyst;

8	3	6	15	8k-15k
20	4	7	40	20k-40k
10	4	7	18	10k-18k
8	3	6	16	8k-16k
10	4	7	18	10k-18k
15	4	7	30	15k-30k
16	4	7	25	16k-25k
20	4	7	35	20k-35k
10	4	7	20	10k-20k
4	3	5	6	4k-6k
15	4	7	25	15k-25k

3.使用substr（）函数代替嵌套；sub(字符串，开始截取位置，截取长度）

SELECT 
left(salary,locate('k',salary)-1),
locate('-',salary),
length(salary),
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1),
salary FROM data.dataanalyst;

4.SQL子查询

case 

when

select
	case 
		when (bottom + top)/2 <=10 then '0-10'
		when (bottom + top)/2 <=20 then '10-20'
		when (bottom + top)/2 <=10 then '20-30'
		else '30+'
	end,
	salary from(
		SELECT 
		left(salary,locate('k',salary)-1) as bottom,
		substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as top,
		salary
		FROM data.dataanalyst) as t

0-10	4k-8k
0-10	8k-10k
0-10	7k-12k
0-10	6k-8k
0-10	6k-10k
0-10	3k-5k
0-10	6k-8k
0-10	6k-8k
0-10	5k-10k