sql - 在左连接中使用 Oracle rank()

Question

当我自己运行这个子查询时:

select * 
from (select rpt_nbr, iteration, seq_nbr, emp_id_key, rank() over
     (partition by   emp_id_key order by iteration, seq_nbr) rk 
      from SJTCAPP.LAB_RPT_SPEC_EMP where rpt_nbr = 1572413) 
where rk = rownum

我得到了一个很好的结果，每次迭代只返回 1 个 emp_id_key 和 seq_nbr，即使可以分配多个 emp_id_key .所以这很好，但是，当我将其添加到我的查询的其余部分时:

select * from 

SJTCAPP.LAB_RPT r 
left join SJTCAPP.LAB_RPT_SPEC s on s.rpt_nbr = r.rpt_nbr
left join (select * from
    (select rpt_nbr, iteration, seq_nbr, emp_id_key, rank() over (partition by emp_id_key order  
         by iteration, seq_nbr) rk from SJTCAPP.LAB_RPT_SPEC_EMP ) where rk = rownum)
         se on se.rpt_nbr = s.rpt_nbr and se.seq_nbr = s.seq_nbr and se.iteration = s.iteration
left join sjtcapp.employee tech on tech.emp_id_key = se.emp_id_key

我得到 tech.emp_id_key 连接的 NULL 值

更新:

select * from (select rpt_nbr, iteration, seq_nbr, emp_id_key, rank() over (partition by emp_id_key order by iteration, seq_nbr ) rk from SJTCAPP.LAB_RPT_SPEC_EMP where rpt_nbr = 1572413)  where rk = rownum and rpt_nbr = 1572413

上述查询也给出了“良好”的结果。

RPT_NBR ITERATION   SEQ_NBR EMP_ID_KEY  RK
1572413 1   1   44746   1
1572413 1   2   44746   2

之前，我更直接地加入这里并收到了包含个别技术人员姓名的正确查询。唯一的问题是，如果分配了多个，则会导致重复，这就是我添加排名子查询的原因。

Answer 1

如果约束迭代并且 seq_nbr 是唯一的，您可以使用 exists 而不是 rank

SELECT
  rpt_nbr,
  iteration,
  seq_nbr,
  emp_id_key
FROM
  SJTCAPP.LAB_RPT_SPEC_EMP emp
WHERE
  NOT EXISTS
    (
      SELECT
        *
      FROM
        SJTCAPP.LAB_RPT_SPEC_EMP emp2
      WHERE
        emp2.emp_id_key = emp.emp_id_key AND
        emp2.iteration < emp.iteration AND
        emp2.seq_nbr < emp.seq_nbr
    )