我想做类似于这样的操作,但是遇到了一个未知列错误:
SELECT SUM(field1 + field2) AS col1, col1 + field3 AS col3 from core
我只想使用别名来避免之前已执行的操作,MySQL是否支持这种操作?
select @code:= SUM(field1 + field2), @code+1 from abc;
请注意以下内容(来自MySQL 5.6文档):
通常情况下,在除 SET 语句以外的语句中,您不应该给用户变量赋值并在同一语句中读取该值。例如,要递增一个变量,可以这样做:
SET @a = @a + 1;
对于其他语句,如SELECT语句,您可能会得到所期望的结果,但这并不是保证的。在下面的语句中,您可能认为MySQL将首先评估@a,然后再进行第二次赋值:
SELECT @a, @a:=@a+1, ...;
然而,涉及用户变量的表达式评估顺序未定义。
因此,请自行决定使用风险。
考虑使用子查询,例如:
SELECT col1
, col1 + field3 AS col3
FROM (
SELECT field1 + field2 as col1
, field3
from core
) as SubQueryAlias
您可以选择别名:select
SELECT SUM(field1 + field2) AS col1, (select col1) + field3 AS col3 from core
这个有效。
SELECT MAX(ID) AS c1, (SELECT c1) AS c2 FROM log GROUP BY ID
中,这个语句会报错(“引用到组函数”的错误)。但是,如果您在此示例中将 MAX(ID)
替换为 ID
,则可以正常工作。 - Christopher K.select @code:= SUM(field1 + field2), (@code*1) from abc;
@code*1 转换为数值表达式后,您可以在任何地方使用它。
select @code:= SUM(field1 + field2), (@code*1)+field3 from abc;
@fVariable:=SUM(column) AS
alias``(别名应该用反引号括起来,因为SO无法正确渲染它们)。 - ReSpawN简短的回答是不可以:
mysql> select 1 as a, a + 1 as b;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'
postgresql# select 1 as a, a + 1 as b;
ERROR: column "a" does not exist
尽管如此,一些SQL实现允许在where/group by/having子句中使用别名,例如:
postgresql# select 1 as a group by a; -- 1 row
SELECT FNC2(AF), FNC1(A) AS AF, B, C, FROM Table GROUP BY ...
1st one doesn't work due to forward column referencing. Do this instead
SELECT FNC1(A) AS AF, B, C, FNC2((SELECT AF)) FROM Table GROUP BY ...
col1
已经是别名的情况下,你还要在select
语句中使用它? - xkeshavSELECT SUM(field1 + field2 + field3) AS coll from core
中存在什么问题? - xkeshav