COALESCE 语法上的 MySQL 条件

Question

我有一个由 COALESCE 语法组成的查询。 COALESCE 语法只是查找文件的审核日期。在我的 WHERE 语句中，我只想从 AuditDate 别名字段输出 Audited File base，但我不起作用。这是我的查询。

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
 WHERE `AuditDate` BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

谁能帮我解决这个查询应该如何正确？

Answer 1

你的查询需要这样

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
 WHERE COALESCE(qua.starttime, prd.starttime) BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

对于您的情况，您不能将来自函数结果的别名用作 WHERE 子句中的查询条件(请参阅 http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html )。但是您可以使用别名在 HAVING 子句中搜索，因为 MySQL 5.0.2 及更高版本允许 HAVING 子句引用 SELECT 中的 select_expr 中命名的任何列或别名列出或在外部子查询中，并聚合函数，如

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
 HAVING `AuditDate` BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

结果相同