MySQL在where子句中使用列别名 [英] Mysql use column alias in where clause

问题描述

我有一个Mysql查询:

I have a Mysql Query:

SELECT created_at as date FROM table

因此，我为created_at列创建了一个别名.现在，我想在我的WHERE子句中使用此别名:

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'

这不起作用. Mysql希望我使用真实的列名.有什么方法可以按照我想要的方式进行吗?

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?

背景:我要从几个表中选择记录，并使用UNION将它们合并.所有表都有一个日期列，但每个表中的名称都不同.但是我想对联合记录执行WHERE，因此我需要始终使用相同的列名

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

推荐答案

如果绝对必须使用别名作为选择条件的一部分，则需要使用HAVING而不是WHERE

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'

但是最好使用列名和WHERE，因为其他人在阅读您的代码时会发现WHERE的含义比HAVING更明显；而且它也不会有效地使用您的表索引

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

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