按别名分组 (Oracle)

2021-12-06 00:00:00 sql oracle

如何使用别名对查询进行分组",例如:

How to 'group by' a query using an alias, for example:

select count(*), (select * from....) as alias_column 
from table 
group by alias_column

我收到alias_column":INVALID_IDENTIFIER 错误消息.为什么?如何对该查询进行分组?

I get 'alias_column' : INVALID_IDENTIFIER error message. Why? How to group this query?

推荐答案

select
  count(count_col),
  alias_column
from
  (
  select 
    count_col, 
    (select value from....) as alias_column 
  from 
    table
  ) as inline
group by 
  alias_column

如果您在 GROUP BY 子句中重复相应的表达式,则分组通常有效.仅提及别名是不可能的,因为 SELECT 步骤是执行查询的最后一步,分组发生得更早,当别名尚未定义时.

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,您将不得不绕道而行并使用嵌套查询,如上所述.

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.

相关文章