如何在CASE WHEN子句内的派生表中使用SELECT别名？

要把主查询的plague别名用到CASE WHEN内的派生表中，你可以通过**公共表表达式（CTE）**先提取基础数据，让后续子查询能直接访问到plague字段。修改后的查询如下：

WITH base_data AS (
    SELECT DISTINCT(`inspectionpointregisters`.`plague_idplague`) AS `plague`,
           (SELECT `plaguecropformulalist`.`formula_idformula`
            FROM `zagros_content`.`plaguecropformulalist` AS `plaguecropformulalist`
            WHERE `plaguecropformulalist`.`plague_idplague` = `plague`
              AND `plaguecropformulalist`.`crop_idcrop` = {0}
              AND `plaguecropformulalist`.`active` = 1) AS `formula_plg_crp`
    FROM `zagros_content`.`inspectionpointregisters` AS `inspectionpointregisters`
    WHERE `inspectionpointregisters`.`inspectionpoints_idinspectionpoints` IN
          (SELECT `inspectionpoints`.`idinspectionpoints`
           FROM `zagros_content`.`inspectionpoints` AS `inspectionpoints`
           WHERE `inspectionpoints`.`inspection_idinspection` = 404)
)
SELECT `plague`,
       `formula_plg_crp`,
       CASE WHEN `formula_plg_crp` = 5 THEN
           (SELECT MAX(`plaguestage`) FROM
                (SELECT DISTINCT `ipr`.`plaguestage_idplaguestage` AS `plaguestage`
                 FROM `zagros_content`.`inspectionpointregisters` AS `ipr`
                 WHERE `ipr`.`inspectionpoints_idinspectionpoints` IN
                       (SELECT `inspectionpoints`.`idinspectionpoints`
                        FROM `zagros_content`.`inspectionpoints` AS `inspectionpoints`
                        WHERE `inspectionpoints`.`inspection_idinspection` = 404)
                   AND `ipr`.`plague_idplague` = base_data.plague) AS `derived_table`)
       ELSE 0 END AS `plg_index_1`
FROM base_data;

关键改动说明： #

• 新增base_data CTE，提前计算出plague和formula_plg_crp的值，让后续查询能直接引用这两个字段。
• 把CASE WHEN里冗余的(SELECT formula_plg_crp)直接替换为formula_plg_crp，简化写法。
• 给派生表中的inspectionpointregisters重命名为ipr避免表名冲突，同时用base_data.plague替换硬编码的207，实现动态引用主查询的别名值。

内容的提问来源于stack exchange，提问作者ConsalterR-1988