ORACLE: ORA-00904：：无效标识符

Question

我创建了以下select：

select e.first_name, e.last_name, 
   (select jobname_id from 
       (select j.id, j.jobname_id, first_value(j.jobname_id) 
        over (order by j.date_from desc) as current_job 
        from jobs j where j.emp_id=e.emp_id and j.date_from < sysdate) 
    where jobname_id != current_job and rownum=1) as previous_job 
from employees e

但是我得到"ORA-00904："E"."EMP_ID"：无效的标识符“

如何在子查询中使用对E.EMP_ID的引用？

Answer 1

我不知道这个解决方案是否足够快来满足您的需求，但它应该按计划工作。此外，我同意奥利关于附加字段"date_to“的看法。这会让事情变得更容易。

select
  e.first_name   as first_name,
  e.last_name    as last_name,
  sub.jobname_id as jobname_id
from 
  employees e,
  (
    select
      j.emp_id     as emp_id,
      j.jobname_id as jobname_id,
      row_number()
        over (
          partition by j.emp_id
          order by     j.date_from desc
        ) as job_order
    from
      jobs j
    where j.date_from < sysdate
  ) sub
where sub.emp_id    = e.emp_id
  and sub.job_order = 2

Answer 2

您不能，但您可以将连接移到外部的子查询中。不过，我不确定它是否能很好地用于分析函数。

select e.first_name, e.last_name, 
   (select jobname_id from 
       (select j.emp_id, j.id, j.jobname_id, first_value(j.jobname_id) 
        over (order by j.date_from desc) as current_job 
        from jobs j where j.date_from < sysdate) j
    where j.emp_id=e.emp_id and jobname_id != current_job and rownum=1) as previous_job 
from employees e