SQL笔记2【Mysql】

将工资进行等级分类——数据清洗 || 套用子查询

select 
    case 
        when (bottom + top)/2 <= 10 then '0~10'
        when (bottom + top)/2 <= 20 then '10~20'
        when (bottom + top)/2 <= 310 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

运行结果：