您的位置: 首页  >  技术问答  >  为什么在 Oracle 和 Mysql 中不支持在同一个选择中使用列别名?

为什么在 Oracle 和 Mysql 中不支持在同一个选择中使用列别名?

分类: 技术问答 • 2022-04-15 13:06:36
问题描述：

create table t1 (c1 integer);

select c1*3 temp, case when (temp <>3) then 1 else 0 end from t1;

Oracle 和 MySQL 中的查询都失败了，但为什么它们不支持这种类型的查询?

Query fails in both Oracle and MySQL But why they doesn't support this type of queries?

我已经完成了与列别名相关的答案.它在任何地方都解释了支持什么和不支持的内容，并在 where 子句中解释了为什么不支持.但问题是为什么 select 子句不支持它.

I already went through the answers related to column alias. Everywhere its explained what is supported and what is not and reasoning about why not in where clause. But question is about why its not supported in select clause.

答

可以在查询选择列表中使用别名来为列指定不同的名称.您可以在 GROUP BY、ORDER BY 或 HAVING 子句中使用别名来引用列:

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

SELECT SQRT(a*b) AS root FROM tbl_name
  GROUP BY root HAVING root > 0;

SELECT id, COUNT(*) AS cnt FROM tbl_name
  GROUP BY id HAVING cnt > 0;

SELECT id AS 'Customer identity' FROM tbl_name;

标准 SQL 不允许在 WHERE 子句中引用列别名.强加此限制是因为在评估 WHERE 子句时，可能尚未确定列值.例如，以下查询是非法的:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name
  WHERE cnt > 0 GROUP BY id;

WHERE 子句确定哪些行应包含在 GROUP BY 子句中，但它指的是列值的别名，直到行之后才知道已被选中，并按 GROUP BY 分组.

The WHERE clause determines which rows should be included in the GROUP BY clause, but it refers to the alias of a column value that is not known until after the rows have been selected, and grouped by the GROUP BY.

在查询的选择列表中，可以使用标识符或字符串引用字符指定带引号的列别名:

In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:

SELECT 1 AS `one`, 2 AS 'two';

在语句的其他地方，对别名的引用必须使用标识符引用，否则引用将被视为字符串文字.例如，此语句按列 id 中的值分组，使用别名 a 引用:

Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal. For example, this statement groups by the values in column id, referenced using the alias a:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
  GROUP BY `a`;

但是此语句按文字字符串 'a' 分组并且不会按预期工作:

But this statement groups by the literal string 'a' and will not work as expected:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
  GROUP BY 'a';

来源:https://docs.oracle.com/cd/E17952_01/refman-5.0-en/problems-with-alias.html

相关推荐