SQL 按别名分组 (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/268429/
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
Group by alias (Oracle)
提问by Ivan Bosnic
How to 'group by' a query using an alias, for example:
如何使用别名对查询进行“分组”，例如：
select count(*), (select * from....) as alias_column
from table
group by alias_column
I get 'alias_column' : INVALID_IDENTIFIER error message. Why? How to group this query?
我收到 'alias_column' : INVALID_IDENTIFIER 错误消息。为什么？如何对这个查询进行分组？
回答by Tomalak
select
count(count_col),
alias_column
from
(
select
count_col,
(select value from....) as alias_column
from
table
) as inline
group by
alias_column
Grouping normally works if you repeat the respective expression in the GROUP BY clause. Just mentioning an alias is not possible, because the SELECT step is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.
如果您在 GROUP BY 子句中重复相应的表达式，则分组通常有效。仅提及别名是不可能的，因为 SELECT 步骤是执行查询的最后一步，分组发生得更早，当别名尚未定义时。
To GROUP BY the result of a sub-query, you will have to take a little detour and use an nested query, as indicated above.
要对子查询的结果进行 GROUP BY，您将不得不绕道而行并使用嵌套查询，如上所述。
回答by Tony Andrews
Nest the query with the alias column:
使用别名列嵌套查询：
select count(*), alias_column
from
( select empno, (select deptno from emp where emp.empno = e.empno) as alias_column
from emp e
)
group by alias_column;
回答by ian_scho
select count(*), (select * from....) as alias_column
from table
group by (select * from....)
In Oracle you cannot use an alias in a group by clause.
在 Oracle 中，您不能在 group by 子句中使用别名。
回答by Andrew
To use an alias in Oracle you need to ensure that the alias has been defined by your query at the point at which the alias is being used.
要在 Oracle 中使用别名，您需要确保在使用别名时查询已经定义了别名。
The most straightforward way to do this is to simply treat the original query as a subquery -- in this case,
最直接的方法是简单地将原始查询视为子查询——在这种情况下，
select count(*), (select * from....) as alias_column
from table
group by (select * from....)
becomes
变成
select count, alias_column
from
(select count(*) as count, (select * from....) as alias_column
from table)
group by alias_column
I can't speak to the performance implications, but it's very quick to write if you're trying to re-use an alias in your query - throw everything in parentheses and jump up a level...
我无法谈论性能影响，但是如果您尝试在查询中重复使用别名，则编写起来非常快-将所有内容都放在括号中并跳上一个级别...
回答by augre
If you don't have to use an alias you could do it this way:
如果您不必使用别名，您可以这样做：
select
EXTRACT(year from CURRENT_DATE), count(*) from something
group by EXTRACT(year from CURRENT_DATE)
order by EXTRACT(year from CURRENT_DATE)
Instead of using alias and subquery.
而不是使用别名和子查询。
