Mysql 排序 - 排序一些而不是其他？

Question

好的，这是我的查询:

SELECT NAME, 
       DATE_FORMAT(DATE_WRITTEN, "%c/%e/%y") AS written_date, 
       DATE_FORMAT(RETURN_DATE, "%c/%e/%y")  AS return_date 
FROM   `pfp`.`returns` AS `Re` 
       LEFT JOIN `pfp`.`insurance` AS `Insurance` 
              ON ( `insurance`.`id` = `Re`.`INSURANCE_ID` ) 
       LEFT JOIN `pfp`.`remain` AS `Remain` 
              ON ( `remain`.`id` = `Re`.`REMAIN_ID` ) 
       LEFT JOIN `pfp`.`formula` AS `Formula` 
              ON ( `formula`.`id` = `remain`.`FORMULA_ID` ) 
WHERE  `NOT_RETURNED` = 'F' 
       AND `RETURN_DATE` BETWEEN '2014-01-01' AND '2014-08-22' 
ORDER  BY `RETURN_DATE` DESC 
LIMIT  100 

问题是它按日期 14-8-9 向下排序到 14-8-7，然后跳回到 14-8-22 并从那里向下...为什么？

Answer 1

当您按 return_date 排序时，您是按格式化的别名排序。相反，使用表别名来标识您确实需要该列:

WHERE  `NOT_RETURNED` = 'F' 
       AND `RETURN_DATE` BETWEEN '2014-01-01' AND '2014-08-22' 
ORDER  BY re.RETURN_DATE DESC 
LIMIT  100 

我猜它在 re 表中。使用适当的别名。

编辑:

首先搜索列别名的事实是documented :

MySQL resolves unqualified column or alias references in ORDER BY clauses by searching in the select_expr values, then in the columns of the tables in the FROM clause. For GROUP BY or HAVING clauses, it searches the FROM clause before searching in the select_expr values. (For GROUP BY and HAVING, this differs from the pre-MySQL 5.0 behavior that used the same rules as for ORDER BY.)

我可以推测其中的原因(我认为这与 ANSI 标准一致)。 SQL 查询在逻辑上按特定顺序处理，例如from，然后是where，然后是select，然后是order by(省略其他子句)。这种逻辑处理决定了查询是如何编译的以及标识符的含义。逻辑处理解释了为什么 where 子句中不允许使用列别名——从编译器的角度来看，它们尚未被识别。

当涉及到order by时，标识符是由内而外确定的。第一个定义是 select 中的版本，因此它会在转到 from 之前选择该版本。