mysql别名并赋值,一起添加MySQL别名字段

Consider a query similar to:

SELECT sum(EXPR) as total,

sum(EXPR) as total2,

sum(total+total2) as grandtotal

FROM tablename

This comes up and says unknown column total in field list.

Is there anyway to reference the alias fields in a calculation without retyping the sum expression because sum(EXPR) on each side is very long.

解决方案

Here's the order of how things are executed in a database engine.

Note that this is a semantic view of how things are executed, the database might do things in a different order, but it has to produce results as though it was done this way.

First the FROM-part is evaluated, where do I get data from

Then the WHERE-part is evaluated, which rows are we interested in

Then the GROUP BY-part is evaluated, how do we combine the resulting rows

Then the HAVING-part is evaluated, which groups are we interested in

Then the ORDER BY-part is evaluated, which order do we want those rows/groups

Finally, the SELECT-part is evaluated, which columns are we interested in

Some database engines allows you to circumvent this though, by saing "GROUP BY 2" to group by the 2nd column in the SELECT-part, but if you stick to the above order, you should know by now that the reason that your code doesn't work is that there are no columns with the names total or total2 (yet).

In other words, you need to either repeat the two expressions, or find another way of doing it.

What you can do is to use a sub-query (providing you're on a MySQL version that supports this):

SELECT total, total2, total+total2 as grandtotal

FROM (

SELECT sum(EXPR) as total, sum(EXPR) as total2

FROM tablename

) x

Striking out the rest as per the comment.

I don't know much about MySQL though so you might have to alias the sub-query:

...

FROM tablename

) AS x

^-+^

|

+-- add this

Some database engines also disallow using the keyword AS when aliasing subqueries, so if the above doesn't work, try this:

...

FROM tablename

) x

^

|

+-- add this