MySQL使用SELECT DISTINCT时按格式化日期排序报错3065的问题

问题原因 #

当使用SELECT DISTINCT时，MySQL要求ORDER BY子句中的表达式必须是SELECT列表中直接存在的字段，或者是表的原始列。你之前尝试的STR_TO_DATE(formatted_date_col, '%m/%d/%y')是基于别名的二次计算表达式，并没有出现在SELECT的字段列表里，因此触发了3065错误。

可行解决方案 #

方案1：直接使用原始日期列排序（最优）

既然目标是实现日期的正确排序，直接用表中原始的date_col（日期类型）排序即可，无需额外转换。这样既保证排序逻辑正确，又符合SELECT DISTINCT的语法要求：

SELECT DISTINCT M.col1, M.col2, DATE_FORMAT(M.date_col, '%m/%d/%y') AS formatted_date_col
FROM t1 M
ORDER BY col1, M.date_col;

这里M.date_col是表的原始列，即便不在SELECT列表中，MySQL也允许在SELECT DISTINCT的ORDER BY中使用——因为去重逻辑基于你指定的SELECT字段，排序只是对去重后的结果做整理，不影响去重规则。

方案2：子查询先去重，外层排序

如果一定要基于格式化后的字符串转换回日期排序，可以把去重逻辑放在子查询中，外层再执行排序操作，这样就不受SELECT DISTINCT对ORDER BY的限制：

SELECT *
FROM (
    SELECT DISTINCT M.col1, M.col2, DATE_FORMAT(M.date_col, '%m/%d/%y') AS formatted_date_col
    FROM t1 M
) AS sub_query
ORDER BY col1, STR_TO_DATE(formatted_date_col, '%m/%d/%y');

为什么之前的尝试失败 #

你之前用STR_TO_DATE(formatted_date_col, ...)作为排序条件时，这个计算后的表达式并未被包含在SELECT DISTINCT的字段集合里。MySQL处理SELECT DISTINCT时，会先根据SELECT列表的字段完成去重，此时ORDER BY只能使用去重时用到的字段，或者表的原始列，不支持基于别名的二次计算表达式。

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