在左连接中使用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

我得到了一个很好的结果，每次迭代只返回一个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连接中得到了一个空值

更新：

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

我之前使用了一个简单的连接来获取每个技术人员的正确查询结果。唯一的问题是，如果有多个技术人员被分配了同一个任务，会导致查询结果出现重复。因此，我添加了 RANK 子查询。

- Scott

---

如果你在内部选择语句中添加 where rpt_nbr =1572413，会再次得到1个emp_ID_key吗？也许当您删除限制性条件时，您就可以看到一对多的情况。或者，如果您从顶部查询示例中删除 where rpt_nbr = 1572413，它是否按照您所需的方式工作？我怀疑 SJTCAPP.LAB_RPT 或 SJTCAPP.LAB_RPT_SPEC 在连接到内部选择之前会导致出现空值，这会导致 tech.emp_ID_Key 和 se.emp_id_key 之间出现空值。需要查看样本数据才能知道（SQLFiddle.Com在此方面会有所帮助）。 - xQbert

“rk = rownum” 给我留下了很不靠谱的印象。虽然它现在可能按照你的期望工作，但我认为你不能指望它一直如此。在无序结果上，没有保证“rownum”会始终表现一致。 - Allan

我也是这样想的。将查询更改为以不同的方式使用 rank()，并使用 rn = 1 'code'left join (select * from (select rpt_nbr, iteration, seq_nbr, emp_id_key, rank() over (partition by iteration, seq_nbr order by emp_id_key) rn from SJTCAPP.LAB_RPT_SPEC_EMP ) where rn = 1) se on se.rpt_nbr = s.rpt_nbr and se.seq_nbr = s.seq_nbr and se.iteration = s.iteration 'code' - Scott

Rank()在此查询中返回非常大的值，因此我猜我不能使用该方法仅获取一行。我尝试使用分组方法，它可以工作，但是查询需要很长时间。我的问题是每个报告可能会有多个SJTCAPP.LAB_RPT_SPEC_EMP，但我不想重复计算。 - Scott

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
    )

- Okdel

---

我认为这是正确的方向。当我尝试单独查询时，仍然返回了额外的员工。我的目标是每个迭代/序列对只有一个员工。 RPT_NBR ITERATION SEQ_NBR EMP_ID_KEY 1572413 1 2 282167 1572413 1 1 282167 1572413 1 2 44746 1572413 1 1 44746 1572413 1 2 227225 1572413 1 1 227225 - Scott

Answer 2

最终我又回到了使用 GROUP BY。

left join (
    select min(rpt_nbr) as rpt_nbr, min(iteration) as iteration, min(seq_nbr) as seq_nbr, min(emp_id_key) as emp_id_key from LAB_RPT_SPEC_EMP group by rpt_nbr, iteration, seq_nbr
    ) se
    on se.rpt_nbr = s.rpt_nbr and se.seq_nbr = s.seq_nbr and se.iteration = s.iteration

- Scott