有没有办法在 Oracle 11g SQL 中给子查询一个别名? [英] Is there a way to give a subquery an alias in Oracle 11g SQL?

问题描述

有没有办法给 Oracle 11g 中的子查询一个别名，例如:

Is there a way to give a subquery in Oracle 11g an alias like:

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;

否则有没有办法根据client_ref_id加入两个子查询.我意识到有一个自联接，但在我运行的数据库上，自联接最多可能需要 5 分钟才能完成(我正在运行的实际查询中有一些额外的逻辑，但我已经确定自联接是什么导致问题).单个子查询只需几秒钟即可自行完成.自连接查询类似于:

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:

select st.request, st1.request
from
    some_table st, some_table st1
where 
    st.client_ref_id = st1.client_ref_id;

推荐答案

您可以使用 CTE (Common Table Expressions) aka WITH 子句 aka by Oracle as Subquery Factoring 为查询指定名称或别名:

You can give a query a name or alias with CTE’s (Common Table Expressions) aka WITH clause aka by Oracle as Subquery Factoring:

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;

这篇关于有没有办法在 Oracle 11g SQL 中给子查询一个别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！