sql - 我如何让我的薪水级别切换用例变为有效的列名

Question

select concat(first_name,' ',last_name) as name,J.job_title, salary, J.min_salary, J.max_salary,(case 
when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.9) then 'SS' 
when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.7) then 'S' 
when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.5) then 'A' 
when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.3) then 'B' 
when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.1) then 'C'
else 'D' end) as salary_level, salary_score = case salary_level
when 'SS' then 1
when 'S' then 2
when 'A' then 3
when 'B' then 4
when 'C' then 5
when 'D' then 6 end from employees E inner join jobs J  on E.job_id = J.job_id order by  salary_score

系统在案例方法“salary_score =案例salary_level ...”中显示无效的列名“salary_level”

Answer 1

您不能指向对查询中生成的列的引用。您可以重新使用相同的CASE语句生成1到6，如下所示-

select concat(first_name,' ',last_name) as name,J.job_title, salary, J.min_salary, J.max_salary,
(case 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.9) then 'SS' 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.7) then 'S' 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.5) then 'A' 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.3) then 'B' 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.1) then 'C'
    else 'D' 
end) as salary_level, 
salary_score = (case 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.9) then 1 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.7) then 2 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.5) then 3 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.3) then 4 
    when salary >= (J.min_salary + (J.max_salary - J.min_salary)*0.1) then 5
    else 6 
end) 
from employees E inner join jobs J  on E.job_id = J.job_id order by  salary_score