How to alias a field or column in MySQL?

问题:

I'm trying to do something like this. But I get an unknown column error:

SELECT SUM(field1 + field2) AS col1, col1 + field3 AS col3 from core

Basically, I want to just use the alias so that I won't need to perform the operations performed earlier. Is this possible in mysql?

回答1:

Consider using a subquery, like:

SELECT col1
,      col1 + field3 AS col3 
FROM   (
       SELECT  field1 + field2 as col1
       ,       field3
       from    core
       ) as SubQueryAlias

回答2:

select @code:= SUM(field1 + field2), @code+1 from abc;

But, please be aware of the following (from the MySQL 5.6 docs):

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

So, use at your own risk.

回答3:

You can select the alias:

SELECT SUM(field1 + field2) AS col1, (select col1) + field3 AS col3 from core

This works.

回答4:

According to the spec Ravi Parekh's answer is not guaranteed to always work, as "order of evaluation for expressions involving user variables is undefined".

I found this answer after I tried to use a variable and got weird results.

回答5:

select @code:= SUM(field1 + field2), (@code*1) from abc;

@code*1 covert into numeric expression and you can use anywhere like

select @code:= SUM(field1 + field2), (@code*1)+field3 from abc;

回答6:

Short answer is no:

mysql> select 1 as a, a + 1 as b;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

postgresql# select 1 as a, a + 1 as b;
ERROR:  column "a" does not exist

That said, some SQL implementations allow to use the aliases in where/group by/having clauses, e.g.:

postgresql# select 1 as a group by a; -- 1 row