在Oracle SQL中的Case语句部分时使用别名 [英] Using Alias In When Portion of a Case Statement in Oracle SQL

问题描述

如果可以在Oracle SQL的case语句中稍后使用的情况下使用select语句中前面提到的别名，那么我一直在尝试查找一段时间.我发现的大多数结果都是关于如何基于不相同的问题的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"完成).还是对select进行一些重做使其嵌套在select语句中更好?可能可行，只是有点痛苦.

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
);

这篇关于在Oracle SQL中的Case语句部分时使用别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！