在同一个 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屋！