oracle:可以为from子句分配别名吗? [英] oracle: can you assign an alias to the from clause?

问题描述

您可以为from子句分配别名吗?像:

can you assign an alias to the from clause? like:

select a - b "Markup" from retail a, cost b;

对不起，我键入的速度太快了，试图将问题简化到没有任何意义的地方

sorry i typed that out a bit too quick and tried to simplify the question to the point where it didnt make any sense

即时通讯实际上试图做的是使用别名来比较同一表中两个发布日期之间的月份.这是我发现有效的方法:

What im actually trying to do is use aliases to compare the months between two publishing dates in the same table. Here's what i found works:

select distinct to_char(months_between((select distinct pubdate
                                        from books3 
                                        where pubid = 2), 
                                       (select distinct pubdate 
                                        from books3 
                                        where pubid = 4)), '99.99') "Answer"
                              from books3

我希望它看起来像这样:

i wanted it to looks something like this:

select distinct months_between(a,b)
from (select distinct pubdate 
       from books3 
       where pubid = 2 as a), 
     (select distinct pubdate 
      from books3 
      where pubid = 4 as b)

但这不起作用

推荐答案

是的，Oracle支持表别名.它在SELECT列表中支持AS，但在FROM列表中不支持:

Yes, Oracle supports table aliases. It supports AS in the SELECT list but not in the FROM list:

SELECT a.col - b.col AS markup
  FROM RETAIL a,
       COST b
 WHERE b.id = a.id

大多数数据库都支持省略AS关键字.

Most databases support omitting the AS keyword.

也就是说，表别名不是列别名-您仍然需要在SELECT子句中引用相应表中的特定列，就像您在示例更新中看到的那样.我还添加了WHERE条件，因此查询不会返回笛卡尔积.

That said, table aliases aren't column aliases -- you still need to reference a specific column in the respective table in the SELECT clause, like you see in my update of your example. I also added the WHERE criteria so the query wouldn't be returning a Cartesian product.

有时派生表/内联视图需要表别名(AKA子查询，尽管我发现术语非常模糊):

Table aliases are sometimes required for derived tables/inline views (AKA subquery, though I find the terminology very vague):

SELECT x.col
  FROM (SELECT t.col,
               MAX(t.date)
          FROM TABLE t
      GROUP BY t.col) x

这是您的查询:

您的问题是，当您需要将表别名放置在派生表中时，该别名必须位于方括号/括号内:

Here's your query:

Your problem was you were putting the table alias inside the derived table, when it needs to be outside the brackets/parenthesis:

SELECT DISTINCT TO_CHAR(MONTHS_BETWEEN(x.pubdate, y.pubdate), '99.99') AS "Answer"
 FROM (SELECT DISTINCT a.pubdate FROM BOOKS3 a WHERE a.pubid = 2) x,
      (SELECT DISTINCT b.pubdate FROM BOOKS3 b WHERE b.pubid = 4) y

您需要与众不同的原因是由于笛卡尔积.

The reason you need the distinct is because of the Cartesian product.

这篇关于oracle:可以为from子句分配别名吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！