在同一个 SELECT 子句中使用列别名 [英] Using Column Alias in Same SELECT Clause
问题描述
无论如何要在分配给它的同一个 SELECT 子句中使用列别名?例如:
Is there anyway to use a column alias in the same SELECT clause which it is being assigned? For example:
SELECT ord_id, candy_id, price, quantity,
price * quantity AS ext_cost, ext_cost * @tax_rate
返回错误,因为 MySQL 无法识别 ext_cost * @tax_rate
查询中的ext_cost
".如果不可能,是否可以返回包含第一个查询中列出的所有内容的表,而无需编写类似的内容?
returns an error because MySQL does not recognize "ext_cost
" in the ext_cost * @tax_rate
query. If it is not possible, is possible to return a table with everything listed in the first query without having to write something like this?
SELECT ord_id, candy_id, price, quantity,
price * quantity AS ext_cost, (price * quantity) * @tax_rate
基本上,我只是想知道是否可以在 SELECT 查询中重用 ext_cost
.
Basically, I was just wondering if there was anyway to reuse ext_cost
in the SELECT query.
推荐答案
没有办法引用别名,但是可以将表达式赋值给一个变量,然后在同一个 select 子句中引用该变量.
No there isn't a way to refer to aliases, but you can assign the expression to a variable, and then refer to the variable in the same select clause.
在 select 语句中变量赋值总是由中缀运算符 :=
完成.*在 SET
语句中,它可以是 =
或 :=
.
Inside a select statement variable assignment is always done by the infix operator :=
. *In a SET
statement, it can be either =
or :=
.
例如
SELECT
ord_id
, candy_id
, price
, quantity
, @exc_cost := price * quantity AS exc_cost
, @exc_cost * @tax_rate AS my_favourite_field
...
<FROM CLAUSE>
您也可以有条件地执行变量赋值.
You can also conditionally perform variable assignment.
例如
IF(quantity > 90,
@exc_cost := price * quantity * 0.95
, @exc_cost := price * quantity) AS exc_cost
注 1:在没有汇总措施的情况下group by 子句,变量按照列顺序求值:
Note 1: In the absence of aggregate measures & group by clause, the variables are evaluated according to column order:
SELECT @t, @t+2 FROM (SELECT @t := 1) a
产生输出
@t @t+2
1 3
这篇关于在同一个 SELECT 子句中使用列别名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!