oracle 如何将主查询列传递给嵌套子查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24861394/
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
How to pass main query column to nested sub query?
提问by user3852515
I am writing a query, which has nested queryies in select statement as below.
我正在编写一个查询，它在 select 语句中嵌套了查询，如下所示。
Select t1.R1,
(
select * from
(
select t2.R2
from table2 t2
where t2.Condition_1=t1.C1
order by t2.Condition_2 desc
)
where rownum=1
),
t1.R3
from table1 t1
I am trying to pass main queries object into a sub query inside a sub query in select statement.
我试图将主查询对象传递到 select 语句中的子查询内的子查询中。
When I execute this I am getting object invalid error at t1.C1.
当我执行这个时，我在t1.C1.
I am able to pass object of the main table table1to the first sub query, but how can I pass the table1column to sub query inside a sub query?
我可以将主表的对象传递table1给第一个子查询，但是如何将列传递给table1子查询中的子查询？
Can any please help me in this scenario?
在这种情况下，有人可以帮助我吗？
采纳答案by Alex Poole
You can only refer to objects down to one level of subquery, so t1just isn't recognised in the inner subquery.
您只能引用低至一级子查询的对象，因此t1无法在内部子查询中识别。
There are a few ways to do this. Sticking with your current subquery, you can make that into an in-line view and join to that instead:
有几种方法可以做到这一点。坚持使用当前的子查询，您可以将其变成内嵌视图并加入其中：
select t1.r1, t2.r2, t1.r3
from table1 t1
join (
select *
from (
select condition_1, r2
from table2
order by condition_2 desc
)
where rownum = 1
) t2 on t2.condition_1 = t1.c1;
The subquery finds one table2record for each condition_1, based on your ordering criteria; then that single row can be joined to a single row from table1(assuming c1is unique).
子查询根据您的订购条件table2为每个找到一条记录condition_1；然后该单行可以连接到单行table1（假设c1是唯一的）。
Or you could use an analytic function:
或者您可以使用分析函数：
select r1, r2, r3
from (
select t1.r1, t2.r2, t1.r3,
row_number() over (partition by t2.condition_1
order by t2.condition_2 desc) as rn
from table1 t1
join table2 t2 on t2.condition_1 = t1.c1
)
where rn = 1;
This joins the two tables and then decides which of the table2values to retain by looking at the already-joined result set, based on the ordering condition in the analytic function's windowing clause. The inner query run on its own would produce what you saw when you tried to join before, with all the 'duplicates' (not really duplicates in the result set, but multiple rows for each r1/r3pair from table1), with an addition rncolumn that ranks those result set rows within those duplicates; the outer query then filters that to only shows the rows ranked first.
这将连接两个表，然后table2根据分析函数的窗口子句中的排序条件，通过查看已连接的结果集来决定保留哪些值。单独运行的内部查询会产生您之前尝试加入时看到的内容，包括所有“重复项”（结果集中不是真正的重复项，而是每个r1/r3对 from 的多行table1），以及一个rn排名的附加列这些重复项中的那些结果集行；然后外部查询将其过滤为仅显示排名第一的行。
SQL Fiddle demo of both approaches.
If condition_2isn't unique then you'd need to decide how to handle ties - if table2could have two r2values for the same condition_1and condition_2combination. You could look at a different analytic function in that case - rankfor example.
如果condition_2不是唯一的，那么您需要决定如何处理关系 - 如果相同和组合table2可以有两个r2值。在这种情况下，您可以查看不同的分析函数 -例如。condition_1condition_2rank
回答by Mahib
I know its bit old but it may help others. You can take the where clause out from the sub sub query and keep that clause where it is accessible.
我知道它有点旧，但它可能会帮助其他人。您可以从 sub 子查询中取出 where 子句，并将该子句保留在可访问的位置。
Select t1.R1,
(
select * from
(
select t2.R2
from table2 t2
) x
where x.Condition_1=t1.C1
order by x.Condition_2 desc
)
where rownum=1
),
t1.R3
from table1 t1
