Oracle 10g - SQL -优化查询和递归调用

Question

我有一张桌子上有关于记录的信息。每个记录可以有几个草稿。要查询信息以获得目前最高的草稿，请执行以下操作：

select 
    record_id,
    record_name,
    record_date
from 
    RECSTABLE
where 
    record_number = XXXXX
    AND record_draft = (select max(record_draft)
                        from RECSTABLE
                        where record_number = XXXXX)

这会返回正确的结果，但很麻烦。我想做一些更先进的事情。

我有一个列表或record_numbers，我想运行这个查询。问题是我如何递归地完成这个任务，如何优化这个查询？

谢谢

Answer 1

像这样的事怎么样：

SELECT RECORD_ID,
       RECORD_NAME,
       RECORD_DATE
  FROM RECSTABLE r
  INNER JOIN (SELECT RECORD_NUMBER,
                     MAX(RECORD_DRAFT) AS MAX_RECORD_DRAFT
                FROM RECSTABLE
                GROUP BY RECORD_NUMBER) m
    ON (m.RECORD_NUMBER = r.RECORD_NUMBER)
  WHERE r.RECORD_NUMBER IN (xxxxx, yyyyy, zzzzz) AND
        r.RECORD_DRAFT = m.MAX_RECORD_DRAFT;

分享并享受。

Answer 2

不要为此使用解析函数，而是使用最后的聚合函数，因为它是更快。

就像这样：

select max(record_id)   keep (dense_rank last order by record_draft) record_id
     , max(record_name) keep (dense_rank last order by record_draft) record_name
     , max(record_date) keep (dense_rank last order by record_draft) record_date
  from recstable
 where record_number = XXXXX
 group by record_number

Answer 3

select record_id, record_name, record_date from ( select record_id, record_name, record_date , row_number() over ( partition by record_number order by record_draft desc ) as rn from RECSTABLE where record_number in (.....) ) where rn = 1