一起添加MySQL别名字段 [英] Adding MySQL alias fields together

问题描述

考虑类似以下查询:

 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.

无论如何，在计算中是否可以引用别名字段而无需重新键入求和表达式，因为每一侧的sum(EXPR)都非常长.

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.

1. 首先对FROM部分进行评估，我该从哪里获取数据
2. 然后评估WHERE部分，我们对哪些行感兴趣
3. 然后评估GROUP BY部分，我们如何合并结果行
4. 然后评估HAVING部分，我们对哪些组感兴趣
5. 然后评估ORDER BY部分，我们想要这些行/组的顺序
6. 最后，对SELECT部分​​进行了评估，我们对哪些列感兴趣

尽管有些数据库引擎允许您通过说"GROUP BY 2"来按SELECT部分​​中的第二列分组来规避此问题，但是如果坚持上述顺序，那么现在您应该知道您的代码行不通，因为没有名称为total或total2的列.

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.

您可以做的是使用子查询(前提是您使用的是支持此功能的MySQL版本):

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.

尽管我对MySQL不太了解，所以您可能必须为子查询加上别名:

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

...

    FROM tablename
    ) AS x
      ^-+^
        |
        +-- add this

某些数据库引擎在对子查询进行别名别名时也不允许使用关键字AS，因此，如果上述方法不起作用，请尝试以下操作:

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

这篇关于一起添加MySQL别名字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！