找不到 MySQL 列

Question

没有 where 语句的 SQL 查询运行良好并输出良好的结果，但是当我包含 WHERE 条件时，它在“where 子句”中显示未知列“date1”。有什么问题？

SELECT
  IF( e.weekly,
      DATE_ADD(DATE(e.time),
      INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ),
   DATE(e.time)) AS `e.date1`,
   `v`.`lat`,
   `v`.`lng` 
FROM `events` AS `e`
INNER JOIN `venues` AS `v` ON e.venue_id = v.id
WHERE e.date1 > '2010-09-01'

Answer 1

您不能使用 <tablename>.<name> 作为列的别名.而不是 AS e.date1你真的必须使用 AS date1 .(如果您省略反引号来创建别名，则会出现 SQL 语法错误。)

但这只是一个原因。另一个是不能在WHERE中使用别名。条款。

来自documentation :

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column

和

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

但是你可以在 HAVING 中使用别名子句:

SELECT
  IF( e.weekly,
    DATE_ADD(DATE(e.time),
    INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ),
    DATE(e.time)) AS `date1`,
  `v`.`lat`,
  `v`.`lng` 
FROM `events` AS `e`
INNER JOIN `venues` AS `v` ON e.venue_id = v.id
HAVING date1 > '2010-09-01'

或者你重复整个IF where 子句中的语句。