有没有办法在 Oracle 11g SQL 中给子查询一个别名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3057930/
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
Is there a way to give a subquery an alias in Oracle 11g SQL?
提问by Matt Pascoe
Is there a way to give a subquery in Oracle 11g an alias like:
有没有办法给 Oracle 11g 中的子查询一个别名，如：
select *
from
(select client_ref_id, request from some_table where message_type = 1) abc,
(select client_ref_id, response from some_table where message_type = 2) defg
where
abc.client_ref_id = def.client_ref_id;
Otherwise is there a way to join the two subqueries based on the client_ref_id. I realize there is a self join, but on the database I am running on a self join can take up to 5 min to complete (there is some extra logic in the actual query I am running but I have determined the self join is what is causing the issue). The individual subqueries only take a few seconds to complete by them selves. The self join query looks something like:
否则有没有办法根据 client_ref_id 加入两个子查询。我意识到有一个自联接，但在我运行的数据库上，自联接最多可能需要 5 分钟才能完成（我正在运行的实际查询中有一些额外的逻辑，但我已经确定自联接是什么导致问题）。单个子查询只需几秒钟即可自行完成。自连接查询类似于：
select st.request, st1.request
from
some_table st, some_table st1
where
st.client_ref_id = st1.client_ref_id;
回答by jmoreno
You can give a query a name or alias with CTE's (Common Table Expressions) aka WITH clause aka by Oracle as Subquery Factoring:
您可以使用 CTE（公用表表达式）又名 WITH 子句（又名 Oracle 作为子查询因子分解）为查询提供名称或别名：
WITH abc as (select client_ref_id, request from some_table where message_type = 1)
select *
from abc
inner join
(select client_ref_id, response from some_table where message_type = 2) defg
on abc.client_ref_id = def.client_ref_id;
回答by OMG Ponies
I don't have an Oracle instance to test with, but what you posted should be valid ANSI-89 JOIN syntax. Here it is in ANSI-92:
我没有要测试的 Oracle 实例，但是您发布的内容应该是有效的 ANSI-89 JOIN 语法。这是 ANSI-92：
SELECT *
FROM (SELECT client_ref_id, request
FROM SOME_TABLE
WHERE message_type = 1) abc
JOIN (SELECT client_ref_id, request
FROM SOME_TABLE
WHERE message_type = 1) defg ON defg.client_ref_id = abc.client_ref_id
回答by Jeffrey Kemp
Your query should be fine.
您的查询应该没问题。
An alternative would be:
另一种选择是：
select abc.client_ref_id, abc.request, def.response
from some_table abc,
some_table def
where abc.client_ref_id = def.client_ref_id
and abc.message_type = 1
and def.message_type = 2;
I wouldn't be surprised if Oracle rewrote the queries so that the plan would be the same anyway.
如果 Oracle 重新编写查询以便计划无论如何都相同，我不会感到惊讶。
