MySQL UNION联合查询无法使用ORDER BY排序触发1250错误

报错原因 #

• UNION 会将多个SELECT子句的查询结果合并为一个统一的结果集，外层的ORDER BY是作用在合并后的整体结果上的，此时子查询内部定义的表别名r、rp已经不在外层查询的上下文中，无法被识别，因此会抛出1250错误。
• 如果你需要按rp.DateCreated排序，需要先在两个子查询中将该字段显式查询并重命名，避免合并后出现字段歧义。

解决方案 #

方案1：按评论创建时间排序 #

你在两个子查询中已经将评论的创建时间r.DateCreated重命名为ActualDateCreated，直接使用这个合并后全局存在的字段别名排序即可，修改后的语句如下：

(select *, r.ReviewId AS ActualReviewId, r.DateCreated AS ActualDateCreated 
 from review r 
 LEFT join reviewphoto rp ON r.ReviewId = rp.ReviewId 
 where r.DishId = 46)
UNION
(select *, r.ReviewId  AS ActualReviewId, r.DateCreated AS ActualDateCreated 
 from review r 
 RIGHT join reviewphoto rp ON r.ReviewId = rp.ReviewId 
 where r.DishId = 46) 
order by ActualDateCreated desc

方案2：按评论照片创建时间排序 #

如果你的业务需求是按关联的照片创建时间排序，需要先在两个子查询中将rp.DateCreated显式导出并重命名，再用该别名排序：

(select *, r.ReviewId AS ActualReviewId, r.DateCreated AS ActualDateCreated, rp.DateCreated AS PhotoCreatedTime
 from review r 
 LEFT join reviewphoto rp ON r.ReviewId = rp.ReviewId 
 where r.DishId = 46)
UNION
(select *, r.ReviewId  AS ActualReviewId, r.DateCreated AS ActualDateCreated, rp.DateCreated AS PhotoCreatedTime
 from review r 
 RIGHT join reviewphoto rp ON r.ReviewId = rp.ReviewId 
 where r.DishId = 46) 
order by PhotoCreatedTime desc

优化提示 #

你当前使用LEFT JOIN + UNION + RIGHT JOIN的写法是为了实现MySQL不原生支持的FULL OUTER JOIN效果，但两个子查询的WHERE条件都限定了r.DishId = 46，RIGHT JOIN部分会因为r表字段非空过滤掉没有匹配评论的照片，逻辑上和直接用INNER JOIN再去重的结果一致，如果符合你的业务预期可以简化语句降低查询开销。

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