MySQL UNION ALL查询中ORDER BY对别名列失效问题排查

错误原因 #

• 你当前ORDER BY子句中使用的approve_con是子查询T1里的原始列，但外层查询已经通过GROUP BY T1.app_id,T1.end_user完成聚合操作，此时外层结果集不存在approve_con字段（外层聚合后的对应列别名是compl_rec），MySQL无法识别该字段进行排序，导致ORDER BY失效。
• 同理，若直接用payout或reversal排序，也会出现逻辑错误：外层的payout和reversal是聚合后的SUM结果，但MySQL可能优先解析为T1里的原始列，而原始列在聚合后无法直接用于排序。
• 额外潜在问题：原查询中T1.created未做聚合处理，GROUP BY后该列的值是不确定的（若开启ONLY_FULL_GROUP_BY模式会直接报错）；子查询里的'0' AS reversal是字符串类型，会干扰SUM的数值计算。

修正后的查询语句 #

方案一：使用外层聚合列别名排序 #

SELECT SUM(T1.approve_con) AS compl_rec,
SUM(T1.payout) AS payout,
SUM(T1.reversal) AS reversal,
T1.app_id,
T1.end_user,
MAX(T1.created) AS created -- 用聚合函数明确created列的取值逻辑
FROM (
    (SELECT app_id,end_user,created,
    approve_conversions AS `approve_con`,
    0 AS reversal, -- 改为数字类型，避免类型转换
    payout AS payout
    FROM app_end_users 
    WHERE publisher_id=1625 
    AND app_id=57)
UNION ALL
    (SELECT opinongold_survey_response.app_id,
    opinongold_survey_response.user_id AS end_user,
    opinongold_survey_response.movement AS created,
    (CASE WHEN opinongold_survey_response.survey_status= 'Completed' THEN 1 ELSE 0 END) AS `approve_con`,
    (CASE WHEN opinongold_survey_response.survey_status= 'Rejected' THEN 1 ELSE 0 END) AS reversal,
    (CASE WHEN opinongold_survey_response.survey_status= 'Completed' THEN payout ELSE 0 END) AS payout                                  
    FROM opinongold_survey_response
    WHERE  pub_id=1625 
    AND app_id=57)
) AS T1
GROUP BY T1.app_id,T1.end_user
ORDER BY compl_rec DESC, payout DESC, reversal DESC -- 使用外层聚合后的别名排序
LIMIT 0, 10   

方案二：直接使用聚合函数表达式排序 #

SELECT SUM(T1.approve_con) AS compl_rec,
SUM(T1.payout) AS payout,
SUM(T1.reversal) AS reversal,
T1.app_id,
T1.end_user,
MAX(T1.created) AS created
FROM (
    (SELECT app_id,end_user,created,
    approve_conversions AS `approve_con`,
    0 AS reversal,
    payout AS payout
    FROM app_end_users 
    WHERE publisher_id=1625 
    AND app_id=57)
UNION ALL
    (SELECT opinongold_survey_response.app_id,
    opinongold_survey_response.user_id AS end_user,
    opinongold_survey_response.movement AS created,
    (CASE WHEN opinongold_survey_response.survey_status= 'Completed' THEN 1 ELSE 0 END) AS `approve_con`,
    (CASE WHEN opinongold_survey_response.survey_status= 'Rejected' THEN 1 ELSE 0 END) AS reversal,
    (CASE WHEN opinongold_survey_response.survey_status= 'Completed' THEN payout ELSE 0 END) AS payout                                  
    FROM opinongold_survey_response
    WHERE  pub_id=1625 
    AND app_id=57)
) AS T1
GROUP BY T1.app_id,T1.end_user
ORDER BY SUM(T1.approve_con) DESC, SUM(T1.payout) DESC, SUM(T1.reversal) DESC -- 直接用聚合函数指定排序逻辑
LIMIT 0, 10   

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