MySQL合并查询需求：按不同规则排序查询customer表cust_id

正确的SQL查询语句 #

SELECT cust_id FROM (
    SELECT cust_id FROM customer 
    WHERE member = 1 
    ORDER BY `date` DESC
) AS member1
UNION ALL
SELECT cust_id FROM (
    SELECT cust_id FROM customer 
    WHERE member = 0 
    ORDER BY cust_id DESC
) AS member0;

报错原因说明 #

你遇到的"date列不存在"错误，本质是因为直接使用UNION时，若尝试在合并后的结果集层面引用date列排序，第二个子查询（member=0的部分）的结果集中只返回了cust_id，没有date列，导致MySQL无法识别该字段。

关键要点 #

• 必须将每个需要单独排序的子查询用括号封装成独立的派生表，这样各自的排序逻辑会先执行，再合并结果。
• 使用UNION ALL而非UNION：UNION会自动对合并后的结果去重并重新排序，会破坏你需要的"先会员按时间降序，再非会员按ID降序"的顺序；如果业务不需要去重，UNION ALL的执行效率更高。

可选的顺序加固方案 #

如果需要确保MySQL版本迭代或执行计划变化时，结果顺序依然严格符合要求，可以给每个子查询添加一个排序权重列，再整体排序：

SELECT cust_id FROM (
    SELECT cust_id, 1 AS sort_weight 
    FROM customer 
    WHERE member = 1 
    ORDER BY `date` DESC
) AS member1
UNION ALL
SELECT cust_id FROM (
    SELECT cust_id, 2 AS sort_weight 
    FROM customer 
    WHERE member = 0 
    ORDER BY cust_id DESC
) AS member0
ORDER BY sort_weight;

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