如何在子查询中使用列别名？解决‘Unknown column’报错

问题原因 #

SQL的执行顺序决定了：SELECT子句里定义的列别名，不能在同一个SELECT子句的其他表达式里直接引用——数据库会先计算SELECT内的所有表达式，之后才会给结果分配别名。另外你写的'dobirthc'加了单引号，会被当成字符串常量而非列名，哪怕去掉单引号，也会因执行顺序问题报错。

解决方法 #

方法1：用子查询包裹基础查询（推荐） #

把生成dobirthc的逻辑放到子查询里，外层查询再用这个别名计算年龄，同时补上原查询缺失的表关联（原查询未关联wp_woocommerce_order_itemmeta表，会导致笛卡尔积，数据结果异常）：

SELECT 
    dobirthc,
    DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), dobirthc)), '%Y') + 0 AS Age
FROM (
    SELECT
        MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN (
            SELECT dobirth FROM wp_sp_enrolments E WHERE OIM.meta_value=E.id
        ) END) AS dobirthc
    FROM wp_woocommerce_order_items O
    JOIN wp_woocommerce_order_itemmeta OIM ON O.order_item_id = OIM.order_item_id
) AS temp;

方法2：重复计算逻辑（不推荐） #

直接把生成dobirthc的表达式重复到计算年龄的语句中，虽然能运行，但后续修改逻辑时需要改两处，维护性差：

SELECT
    MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN (
        SELECT dobirth FROM wp_sp_enrolments E WHERE OIM.meta_value=E.id
    ) END) AS dobirthc,
    DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN (
        SELECT dobirth FROM wp_sp_enrolments E WHERE OIM.meta_value=E.id
    ) END))), '%Y') + 0 AS Age
FROM wp_woocommerce_order_items O
JOIN wp_woocommerce_order_itemmeta OIM ON O.order_item_id = OIM.order_item_id;

方法3：使用CTE（MySQL 8.0+支持） #

如果你的MySQL版本是8.0及以上，用公共表表达式能让逻辑更清晰：

WITH temp_data AS (
    SELECT
        MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN (
            SELECT dobirth FROM wp_sp_enrolments E WHERE OIM.meta_value=E.id
        ) END) AS dobirthc
    FROM wp_woocommerce_order_items O
    JOIN wp_woocommerce_order_itemmeta OIM ON O.order_item_id = OIM.order_item_id
)
SELECT 
    dobirthc,
    DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), dobirthc)), '%Y') + 0 AS Age
FROM temp_data;

额外优化 #

你当前的年龄计算方式存在缺陷：如果用户生日还没到当年，会多算1岁。更准确的写法是：

TIMESTAMPDIFF(YEAR, dobirthc, NOW()) AS Age

可以替换到上面任意解法中，结果更精准。

内容的提问来源于stack exchange，提问作者user14475135