SQL oracle：你能为 from 子句指定一个别名吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4940530/
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
oracle: can you assign an alias to the from clause?
提问by Sinaesthetic
can you assign an alias to the from clause? like:
你可以为 from 子句指定一个别名吗？喜欢：
select a - b "Markup" from retail a, cost b;
EDIT: 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)
but that isn't working
但这不起作用
回答by OMG Ponies
Yes, Oracle supports table aliases. It supports ASin the SELECTlist but not in the FROMlist:
是的，Oracle 支持表别名。它支持AS在SELECT列表中但不在FROM列表中：
SELECT a.col - b.col AS markup
FROM RETAIL a,
COST b
WHERE b.id = a.id
Most databases support omitting the ASkeyword.
大多数数据库支持省略AS关键字。
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 WHEREcriteria so the query wouldn't be returning a Cartesian product.
也就是说，表别名不是列别名——您仍然需要在 SELECT 子句中引用相应表中的特定列，就像您在我的示例更新中看到的那样。我还添加了WHERE条件，因此查询不会返回笛卡尔积。
Table aliases are sometimes required for derived tables/inline views (AKA subquery, though I find the terminology very vague):
派生表/内联视图有时需要表别名（AKA 子查询，但我发现该术语非常模糊）：
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.
您需要不同的原因是因为笛卡尔积。
回答by RichardTheKiwi
The closest to what you have would be to move the AS aliasout of the subquery
最接近您所拥有的是将AS alias子查询移出
select distinct months_between(a.pubdate,b.pubdate)
from (select distinct pubdate
from books3
where pubid = 2) as a ,
(select distinct pubdate
from books3
where pubid = 4) as b;
But still, the query doesn't make much sense. If there are 2 records for pubid=2and 3 for pubid=4, you get 6 rows in the output....
但是，查询仍然没有多大意义。如果有 2 条记录 forpubid=2和 3条记录pubid=4，则输出中有 6 行....
months_between(a1, b1)
months_between(a2, b1)
months_between(a1, b2)
months_between(a2, b2)
months_between(a1, b3)
months_between(a2, b3)
I suspect you actually have some grouping going on, so this will compare pubid=2 and pubid=4 entries at a per-bookid level.
我怀疑您实际上正在进行一些分组，因此这将在每个 bookid 级别比较 pubid=2 和 pubid=4 条目。
select
bookid,
to_char(months_between(
max(case when pubid=2 then pubdate end),
max(case when pubid=4 then pubdate end)), '99.99') "Answer"
from books
group by bookid;
