oracle Oracle嵌套相关子查询问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5314321/
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 nested correlated subquery problem
提问by GriffeyDog
Consider table1 and table2 with a one-to-many relationship (table1 is the master table and table2 is the detail table). I want to get records from table1 where some value ('XXX') is the value of the most recent record in table2 of the detail records correlated to table1. What I want to do is this:
考虑具有一对多关系的 table1 和 table2（table1 是主表，table2 是详细表）。我想从 table1 获取记录，其中某个值 ('XXX') 是 table2 中与 table1 相关的详细记录的最新记录的值。我想做的是这样的：
select t1.pk_id
from table1 t1
where 'XXX' = (select a_col
from ( select a_col
from table2 t2
where t2.fk_id = t1.pk_id
order by t2.date_col desc)
where rownum = 1)
But, because the reference to table1 (t1) in the correlated subquery is two-levels deep, it pops up with an Oracle error (invalid id t1). I need to be able to rewrite this, but the one caveat is that only the where clause may be changed (i.e. the initial select and from must remain unchanged). Can it be done?
但是，因为在相关子查询中对 table1 (t1) 的引用是两级深的，所以它会弹出一个 Oracle 错误（无效的 id t1）。我需要能够重写这个，但一个警告是只有 where 子句可以改变（即初始的 select 和 from 必须保持不变）。可以做到吗？
回答by Dave Costa
Here's a different analytic approach:
这是一种不同的分析方法：
select t1.pk_id
from table1 t1
where 'XXX' = (select distinct first_value(t2.a_col)
over (order by t2.date_col desc)
from table2 t2
where t2.fk_id = t1.pk_id)
And here's the same idea using a ranking function:
这是使用排名函数的相同想法：
select t1.pk_id
from table1 t1
where 'XXX' = (select max(t2.a_col) keep
(dense_rank first order by t2.date_col desc)
from table2 t2
where t2.fk_id = t1.pk_id)
回答by Vincent Malgrat
you could use analytics here: join table1 to table2, take the most recent table2 record for each element in table1 and verify that this most recent element has a value of 'XXX':
您可以在此处使用分析：将 table1 连接到 table2，为 table1 中的每个元素获取最新的 table2 记录，并验证此最新元素的值是否为“XXX”：
SELECT *
FROM (SELECT t1.*,
t2.a_col,
row_number() over (PARTITION BY t1.pk
ORDER BY t2.date_col DESC) rnk
FROM table1 t1
JOIN table2 t2 ON t2.fk_id = t1.pk_id)
WHERE rnk = 1
AND a_col = 'XXX'
Update: Without modifying the top-level SELECT, you could write a query like this:
更新：无需修改顶级 SELECT，您可以编写如下查询：
SELECT t1.pk_id
FROM table1 t1
WHERE 'XXX' =
(SELECT a_col
FROM (SELECT a_col,
t2_in.fk_id,
row_number() over(PARTITION BY t2_in.fk_id
ORDER BY t2_in.date_col DESC) rnk
FROM table2 t2_in) t2
WHERE rnk = 1
AND t2.fk_id = t1.pk_id)
Basically you only join (SEMI-JOIN) the rows from table2 that are the most recent for each fk_id
基本上你只加入（半加入）table2 中每个最近的行 fk_id
回答by Lost in Alabama
Try this:
尝试这个：
select t1.pk_id
from table1 t1
where 'XXX' =
(select a_col
from table2 t2
where t2.fk_id = t1.pk_id
and t2.date_col =
(select max(t3.date_col)
from table2 t3
where t3.fk_id = t2.fk_id)
)
回答by Jacob Schoen
Does this do what you are looking for?
这是否符合您的要求？
select t1.pk_id
from table1 t1
where 'XXX' = ( select a_col
from table2 t2
where t2.fk_id = t1.pk_id
t2.date_col = (select max(date_col) from table2 where fk_id = t1.pk_id)
)
