在 Oracle SQL 中 Case 语句的 When 部分中使用别名

2021-11-27 00:00:00 sql alias oracle case

我一直在尝试查找是否可以使用之前在 select 语句中声明的别名，如果它可以在稍后在 Oracle SQL 的 case 语句中使用.我发现的大多数结果都是关于如何根据 case 语句创建别名，这不是同一个问题.一个简单的例子是这样的:

I've been trying to look up for awhile now if it's possible to use an alias stated earlier in the select statement if it can be used in a case later in the case statement for Oracle SQL. Most results I find are about how to make an Alias based on a case statement which isn't the same problem. A quick example would be something like:

Select TABLEA.SomeIDNumber AS "Id",
       case ID
          when 3
          then 'foo'
          else 'bar'
       end AS "Results"
FROM OMEGA.TABLEA

在我创建的 SQL 语句中，它真的没有那么简单(它实际上是基于前面的 case 语句创建的，并且需要对各种表进行一些连接来完成查询的其他全部内容，但它不会真正使在不了解更多我无法分享的数据库的情况下感觉很有意义).

It's really not that simple in the SQL Statement I'm creating (it's actually created based on a previous case statement and requires some joins on various tables to do the full other pats of the query, but it wouldn't really make sense without knowing more of the database which I can't share).

我只是想知道是否可以在稍后在 Oracle 的 select 语句中的 case 语句中使用别名(我知道这样的事情可以用 Access 有点SQL"来完成).或者我对选择进行一些修改以使其嵌套选择语句更好吗?可能是可行的，只是有点痛苦.

I'm just wondering if it's possible to use an alias in a case statement later in the select statement for Oracle (I know such things can be done with Access kinda "SQL"). Or is it better for me to do some reworking of the select to make it nested select statements? Probably doable, just a bit more of a pain.

推荐答案

不行，你不能在 select 的同级中的其他地方引用别名，除了在 order 中by 子句，因为 Oracle 在内部分配它.

No, you can't refer to the alias elsewhere in the same level of select, other than in the order by clause, because of when Oracle assigns it internally.

来自文档(强调添加):

您可以使用列别名 c_alias 来标记立即选择列表中的前面的表达式，以便该列是以新标题显示.别名有效地重命名了选择查询期间的列表项.别名可以用在ORDER BY 子句，但不是查询中的其他子句.

You can use a column alias, c_alias, to label the immediately preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query.

您需要使用内部查询，例如:

You would need to use an inner query, something like:

select "Id",
    case "Id"
        when 3
        then 'foo'
        else 'bar'
    end AS "Results"
from (
    select TABLEA.SomeIDNumber AS "Id",
    from TABLEA
);

相关文章