MySQL 关于 COALESCE 语法的条件

发布于 2024-12-08 21:54:15 字数 495 浏览 0 评论 0原文

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

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';

任何人都可以帮助我如何正确地获得此查询？

原文

I have a query composing of COALESCE syntax. the COALESCE syntax simply find the Audited Date of a file. On my WHERE statement, I just want to output the Audited File base from the AuditDate alias field but i doesn't work. Here is my query.

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';

Can anyone help me on how should I get this query correct?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

木槿暧夏七纪年 2024-12-15 21:54:15

您的查询需要像这样适合

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';

两者都有相同的结果

Your query need to be like this

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';

for your situation you can't use the alias that came from a result of a function as query criteria in WHERE clause (see http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html). But you can use the alias to search in HAVING clause, as MySQL 5.0.2 and up permit HAVING clause to refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions, like

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';

both have same result

回复收藏 0 原文

聆听风音 2024-12-15 21:54:15

使用

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';

上面假设您的日期格式/列类型确实匹配...

use

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';

The above assumes that your date format/column types really match...

回复收藏 0 原文

﹂绝世的画 2024-12-15 21:54:15

您不能像其他人指出的那样使用别名。为了避免调用合并两次，您可以使用 HAVING 语句。

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';

You cannot use an alias as others have pointed out. To avoid calling coalesce two times, you can use a HAVING statement.

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';

回复收藏 0 原文

另类 2024-12-15 21:54:15

您不能在 WHERE 子句中使用 SELECT 列表中的别名。

使用这个：

SELECT *
FROM
  ( 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
  ) AS tmp
WHERE `AuditDate` BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

或复制代码：

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';

You can't use an alias from the SELECT list in the WHERE clause.

Use this:

SELECT *
FROM
  ( 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
  ) AS tmp
WHERE `AuditDate` BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

or duplicate the code:

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';

回复收藏 0 原文

~没有更多了~