有没有办法在 Oracle 11g SQL 中为子查询指定别名？

发布于 2024-09-06 14:34:50 字数 584 浏览 8 评论 0原文

有没有办法给 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;

否则有没有办法根据 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;

原文

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;

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

独夜无伴 2024-09-13 14:34:51

您可以使用 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;

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;

回复收藏 0 原文

温柔女人霸气范 2024-09-13 14:34:51

我没有要测试的 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

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:

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

回复收藏 0 原文

无名指的心愿 2024-09-13 14:34:51

你的查询应该没问题。

另一种选择是：

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;

如果 Oracle 重写查询以使计划保持不变，我不会感到惊讶。

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.

回复收藏 0 原文

怪我入戏太深 2024-09-13 14:34:51

在 SQL 标准中，您可以通过以下方式定义子查询的别名

SELECT Temp.patente, Temp.SalarioProm
FROM ( SELECT A.aid 援助, A.patente AS 专利,
AVG (E.esalario) AS SalarioProm
来自飞机 A、认证 C、员工 E
哪里 A.aid = C.aid
AND C.eid = E.eid
和 A.rangocrucero > 1000
GROUP BY A.aid, A.patente ) AS Temp