在MySQL中过滤别名 [英] Filtering on an alias in mysql

问题描述

为什么以下查询不起作用? Mysql抱怨z-我不能在WHERE子句中使用别名吗?

Why doesn't the following query work? Mysql complains about z - can't I use an alias in the WHERE clause?

SELECT x + y AS z, t.*  FROM t
WHERE 
x = 1 and
z = 2

我得到的错误是:

Error Code : 1054
Unknown column 'z' in 'where clause'

推荐答案

http ://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

标准SQL不允许引用 WHERE子句中的列别名.这 施加限制是因为 对WHERE子句进行求值， 列值可能尚未 决定.例如，以下 查询是非法的:

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. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

尝试以下方法:

SELECT x + y AS z, t.* FROM t WHERE x = 1 HAVING z = 2;

这篇关于在MySQL中过滤别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！