mysql - 带有计算值的 SQL WHERE 子句

Question

看来我不能在 WHERE 子句中使用计算值？

SELECT id, TIMESTAMPDIFF(YEAR,dateOfBirth,CURDATE()) AS age 
FROM user
WHERE age >= 20

我收到 ERROR 1054 (42S22): Unknown column 'age' in 'where clause'。一种可能的解决方法是使用子查询，但它过于复杂？

Answer 1

您可以在 WHERE 子句中使用计算值，但不能使用其别名。你必须再次输入整个表达式

WHERE TIMESTAMPDIFF(YEAR,dateOfBirth,CURDATE()) >=20

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.

Reference