mysql ERROR 3065

报错语句

select distinct order_num, prod_id from orderitems order by order_item;

报错详情

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'Mydb.orderitems.order_item' which is not in SELECT list; this is incompatible with DISTINCT

报错原因

• mysql中distinc执行顺序高于order by
• distinct查询时产生一张虚拟临时表
• order by在虚拟临时表中找不到排序字段，因而报错

参考
mysql distinct和order by 一起用时，order by的字段必须在select中
mysql Unknown error 3065
解决方案
mysql5.7 DISTINCT 后 order by 报错：[3065] 快速解决方案