Oracle嵌套相关子查询问题

发布于 2024-10-22 02:25:18 字数 570 浏览 23 评论 0原文

考虑具有一对多关系的 table1 和 table2（table1 是主表，table2 是从表）。我想从 table1 获取记录，其中某个值（'XXX'）是与 table1 相关的详细记录的 table2 中最新记录的值。我想做的是这样的：

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)

但是，由于相关子查询中对 table1 (t1) 的引用是两级深，因此会弹出 Oracle 错误（无效的 id t1）。我需要能够重写它，但需要注意的是，只有 where 子句可以更改（即初始 select 和 from 必须保持不变）。能做到吗？

原文

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:

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?

分享到QQ

分享到微博

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

发布评论

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

执手闯天涯 2024-10-29 02:25:18

这是一种不同的分析方法：

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)

这是使用排名函数的相同想法：

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)

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)

回复收藏 0 原文

微凉 2024-10-29 02:25:18

您可以在此处使用分析：将 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'

更新：不修改顶部-level 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)

基本上，您只连接（半连接）table2 中每个 fk_id 最新的行

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':

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 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

回复收藏 0 原文

信愁 2024-10-29 02:25:18

试试这个：

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)
)

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)
)

回复收藏 0 原文

一杯敬自由 2024-10-29 02:25:18

这符合您的要求吗？

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)
                )

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)
                )

回复收藏 0 原文

~没有更多了~