MySQL - 在 SELECT 中使用 'AS var_name'

Question

如何在稍后的同一选择中使用诸如“AS uniqueName”之类的变量？

我试图在选择中放入一些简单的逻辑，这样我就不必为了做一些简单的事情而迭代结果集:

SELECT
CASE emp_crns.ansi_class
    when -1 then 'N'
    when 150 then 0
    when 300 then 1
    when 600 then 2
    when 900 then 3
    when 1500 then 4
    when 2500 then 6
    when 3500 then 8
    else 'X'
end AS crnAnsiClassCode,
CONCAT('REV. ', crnAnsiClassCode, ' ', emp_models.name) AS modelName
FROM
emp_models
JOIN
emp_revisions ON emp_revisions.id=emp_models.revision_id
JOIN
emp_crns ON emp_crns.id=emp_revisions.crn_id

Answer 1

您可以使用子查询来访问您提供的别名:

SELECT empcrns.crnAnsiClassCode,
    CONCAT('REV. ', empcrns.crnAnsiClassCode, ' ', emp_models.name) AS modelName
FROM emp_models
JOIN emp_revisions 
    ON emp_revisions.id=emp_models.revision_id
JOIN
(
    SELECT id, 
        CASE ansi_class
            when -1 then 'N'
            when 150 then 0
            when 300 then 1
            when 600 then 2
            when 900 then 3
            when 1500 then 4
            when 2500 then 6
            when 3500 then 8
            else 'X'
        end AS crnAnsiClassCode
    FROM emp_crns
) empcrns
    ON empcrns.id=emp_revisions.crn_id