JOIN语句表别名正确仍报#1054未知列错误，求排查解决

错误原因 #

报错Unknown column 'o.customer_id' in 'on clause'的核心问题有两个：

1. 子查询内部别名无法外部引用：o是子查询里order表的别名，仅在子查询内部有效，外部JOIN语句无法访问这个别名。
2. 子查询未返回关联所需字段：关联customer表需要customer_id，但子查询o2的SELECT列表里没包含该字段，且GROUP BY也未加入（聚合查询中，非聚合字段必须出现在GROUP BY里，否则MySQL严格模式下会触发报错）。

修正步骤 #

1. 子查询补充customer_id字段：在子查询的SELECT中添加o.customer_id，同时GROUP BY要加上o.customer_id（确保聚合逻辑的严谨性，若一个order_id对应唯一customer_id，仅GROUP BY o.order_id也可，但添加后兼容性更强）。
2. 修正JOIN关联条件：将LEFT JOIN customer AS c ON o.customer_id = c.customer_id改为LEFT JOIN customer AS c ON o2.customer_id = c.customer_id，使用子查询返回的o2表的customer_id进行关联。
3. 调整GROUP BY字段：MySQL 5.7+严格模式要求GROUP BY包含所有SELECT中的非聚合字段，因此GROUP BY需补充c.customer_lastname, c.customer_firstname, ci.customer_street_address, ci.customer_phone，避免潜在报错。

修正后的SQL #

SELECT 
    c.customer_id, 
    c.customer_lastname, 
    c.customer_firstname, 
    SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0)) AS sold_products_count, 
    SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) AS returned_products_count, 
    ROUND(100 * SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) 
        / SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0))) AS returned_products_percent, 
    ci.customer_street_address, 
    ci.customer_phone
FROM 
    `order_products` AS op 
    LEFT JOIN (
            SELECT o.order_id, o.customer_id, MIN(o.order_datetime) AS first_order_date, 
            MAX(o.order_datetime) AS last_order_date, 
            DATEDIFF(NOW(), MAX(o.order_datetime)) AS days_since_last_order, 
            COUNT(DISTINCT o.order_id) AS orders_count, 
            SUM(o.order_total) AS orders_total
            FROM `order` AS o
            GROUP BY o.order_id, o.customer_id) AS o2 ON o2.order_id = op.order_id
        LEFT JOIN customer AS c ON o2.customer_id = c.customer_id  
        LEFT JOIN customer_address AS ci USING (customer_id) 
GROUP BY 
    c.customer_id, c.customer_lastname, c.customer_firstname, ci.customer_street_address, ci.customer_phone
ORDER BY 
    c.customer_id ASC LIMIT 100

额外提示 #

returned_products_percent可能出现除以0的情况（当sold_products_count为0时），可以用IFNULL()和NULLIF()处理，比如改成：

ROUND(100 * IFNULL(SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) / NULLIF(SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0)), 0), 0)) AS returned_products_percent

避免触发除以0的报错。

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