MySQL 在 SQL 计算中使用别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2077475/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using an Alias in SQL Calculations
提问by Tom Rossi
Why won't this query work?
为什么这个查询不起作用？
SELECT 10 AS my_num, my_num*5 AS another_number
FROM table
In this example, I'm trying to use the my_num alias in other calculations. This results in unknown column "my_num"
在此示例中，我尝试在其他计算中使用 my_num 别名。这导致未知列“my_num”
This is a simplified version of what I am trying to do, but basically I would like to use an alias to make other calculations. My calculations are much more complicated and thats why it would be nice to alias it since I repeat it several times different ways.
这是我正在尝试做的简化版本，但基本上我想使用别名进行其他计算。我的计算要复杂得多，这就是为什么最好将其别名化，因为我以不同的方式重复了几次。
回答by zessx
Simply wrap your reused alias with (SELECT alias):
只需使用以下命令包装您重用的别名(SELECT alias)：
SELECT 10 AS my_num,
(SELECT my_num) * 5 AS another_number
FROM table
回答by Rubens Farias
You'll need to use a subselect to use that aliases that way
您需要使用子选择来以这种方式使用该别名
SELECT my_num*5 AS another_number FROM
(
SELECT 10 AS my_num FROM table
) x
回答by Rob Van Dam
Aliases in sql are not like variables in a programming language. Aliases can only be referenced again at certain points (particularly in GROUP BYand HAVINGclauses). But you can't reuse an alias in the SELECTclause. So you can use a derived query (such as suggested by Rubens Farias) which lets you basically rename your columns and/or name any computed columns.
sql 中的别名不像编程语言中的变量。别名只能在某些地方再次引用（特别是在GROUP BYandHAVING子句中）。但是您不能在SELECT子句中重复使用别名。因此，您可以使用派生查询（例如Rubens Farias建议的），它可以让您基本上重命名列和/或命名任何计算列。
Or you could use a VIEWif your formulas are generally fixed
或者，VIEW如果您的公式通常是固定的，您可以使用 a
CREATE VIEW table10 AS SELECT 10 AS my_num FROM table;
SELECT my_num * 5 AS another_number FROM table10;
I believe that will be slightly faster than using a derived query but it probably depends a lot on your real query.
我相信这会比使用派生查询稍快，但它可能在很大程度上取决于您的真实查询。
Or you could just duplicate the work:
或者你可以复制工作：
SELECT 10 AS my_num, 10 * 5 AS another_number FROM table;
Which might be convenient in something like php/perl:
这在 php/perl 之类的东西中可能很方便：
my $my_num = 10;
my $query = "SELECT $my_num AS my_num, $my_num * 5 AS another_number FROM table";
回答by Freddy Jose Chirinos Moròn
``Another option is to use the APPLY operator
``另一种选择是使用 APPLY 运算符
SELECT my_num, my_num*5 AS another_number
FROM table
CROSS APPLY
(SELECT 5 AS my_num) X
