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
The e开发者_运维问答rror that I get is:
Error Code : 1054
Unknown column 'z' in 'where clause'
http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html
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;
Try this, instead:
SELECT x + y AS z, t.* FROM t WHERE x = 1 HAVING z = 2;
You should be aware that the usage of the HAVING clause without a GROUP BY clause is a non-standard extension of MySQL and won't work in other databases.
If you want this to be portable, you need to use a derived table:
SELECT * FROM ( SELECT (x + y) AS z, t.* FROM t WHERE x = 1 ) t2 WHERE z = 2
Use having clause:
SELECT (x + y) AS z, t.* FROM t
WHERE
x = 1
having z=2
精彩评论