mysql where如何引用select中的别名字段,MySQL在where子句中使用列别名

I have a Mysql Query:

SELECT created_at as date FROM table

So I created an alias for the created_at column. Now I want to use this alias in my WHERE clause:

SELECT created_at as date FROM table WHERE date = 'xxxx-xx-xx'

This does not work. Mysql expects me to use the real column name. Is there any way to do it the way I want to?

Background: I am selecting records from several tables and unite them using UNION. All the tables have a date column but it's named different in every table. But I want to do WHERE on the united records and therefore I need to have always the same column name

解决方案

If you absolutely have to use your alias as part of the selection criteria, then you need to use HAVING rather than WHERE

SELECT created_at as date FROM table HAVING date = 'xxxx-xx-xx'

But it's better to use the column name and WHERE because other people reading your code will find the meaning of WHERE more obvious than HAVING; and it won't use your table indexes as efficiently either