SELECT left(salary,locate('k',salary)-1) ,salary from data.datan;取薪资上限的数值
left(字段,位置数字)：在 字段 中 第 位置数字 拿出来
SELECT 取薪资下限的数值
left(salary,locate('k',salary)-1) ,
left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1),
salary from data.datan;
substr(字符串,从哪里截取，截取长度)
SELECT
left(salary,locate('k',salary)-1) ,
left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1),
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1),
salary from data.datan;
sql子查询
当有薪资的上下限后，把这个表作为table，继续查询
SELECT * FROM (
SELECT
left(salary,locate('k',salary)-1) as bottom,
left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1),
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as top,
salary from data.datan) as t
为平均值做分类，用case when 相当于vlookup
SELECT
case when 为顺序判断
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
left(salary,locate('k',salary)-1) as bottom,
left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1),
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as top,
salary from data.datan) as t
查询明细数据（过滤用法）
SELECT * FROM data.datan
where city in ( 使用WHERE 做子查询 in可以换成大小于，但是值要唯一；红色的city要对应
SELECT city FROM data.datan
group by city having count(positionId)>=100)
sql join 多表分析
跨表查数据
SELECT * FROM data.datan
where companyId = (
select companyId from data.company
where companyShortName = '唯医网'
)
在一张表上，将2个表的内容 通过共同有的字段 关联合并
SELECT * FROM data.datan as d
join data.company as c on d.companyId =c.companyId
子查询也可以用join
SELECT * FROM data.datan as d
left join(select * from data.company
where companySize = '150-500' ) as t
on t.companyId =d.companyId