在子select: ORA-00904:无效标识符和ORA-06553:调用中的参数数目或类型错误

Question

我见过很多关于这个问题的帖子，但没有一个解决办法解决了我的问题。

简而言之，我有一个users表和一个user_history表。每个用户可以有0或更多的user_history条目。user_history表有一个status列。我所要做的就是获取用户列表和他们最近的user_history条目的status列的值。我不能让它起作用。我试过：

select u.id, u.name, 
(select status from (select status, rownum as rn from user_history uh where uh.user_id = u.id    
order by created_date desc) where rn = 1) status
from users u;

这给了我一个"ORA-00904:无效标识符，u.id“错误。据我所读，甲骨文不允许你访问外部选择'u.id‘内的一个子-子选择(一个有行)。从第一个子选择它工作良好，但正如我说过，我可以有n个条目在user_history，我只需要最近的。

我还尝试使用内部连接：

select u.id, u.name, h.status
from users u
inner join (select user_id, status, rownum as rn from user_history where user_id = u.id order by created_date desc) h on u.id = h.user_id where h.rn = 1;

这给了我可怕的"ORA-06553:调用u的错误数量或类型的参数“.我试着用不同的方法修复，但没有用。

我还尝试过使用row_number()、over和分区.其他类型的内部连接与选择..。我所需要的数据什么也得不到。

有人能帮我处理这个(看似)简单的查询吗？

Answer 1

在过去，查询应该如下所示

select 
    u.id, 
    u.name, 
    uh.status
from 
    users u 
        inner join
    (select 
         user_id, 
         status 
     from 
         user_history h 
     where 
         created_date = (select 
                             max(created_date) 
                         from 
                             user_history d 
                         where 
                             h.user_id = d.user_id)
     ) uh
         on u.id = uh.user_id;

这里有一个相关的子查询，它将为用户获取历史上的最新日期。它将对每一行执行，因此它的执行速度有点慢。然后将它与您的用户表连接起来，以获得您的状态。我还没试过，但看上去是对的。

Answer 2

像这样的东西有用吗？它还将消除查询中的标量，并且更容易调试，因为您可以独立运行内部查询(呃)并评估其结果。

with uh as (
  select
    u.id, u.name, uh.status, uh.created_date,
    max (uh.created_date) over (partition by uh.user_id) as max_date
  from
    users u,
    user_history uh
  where
    u.id = uh.user_id
)
select
  id, name, status
from uh
where created_date = max_date

-编辑--

至于它的价值，我加载了一些示例数据：

用户

1   Bilbo
2   Fatty
3   Pippin
4   Balin

用户历史

1   one     1/1/2014
1   two     1/2/2014
1   three   1/3/2014
2   four    1/4/2014
2   five    1/5/2014
2   six     1/6/2014
3   seven   1/7/2014
3   eight   1/8/2014
3   nine    1/9/2014

这是产出：

1   Bilbo   three
2   Fatty   six
3   Pippin  nine

如果您有完全相同的“日期”字段的多个历史记录，下面是row_number替代。

with uh as (
  select
    u.id, u.name, uh.status,
    row_number() over 
       (partition by u.id order by uh.created_date desc) as rn
  from
    users u,
    user_history uh
  where
    u.id = uh.user_id
)
select
  id, name, status
from uh
where rn = 1