在具有不连续范围的表 SQL 中查找下一个未使用的 ID

Question

我有两个表，如下所示：

TABLE1:
=======
somid, tobeupdated
1    ,  null
2    ,  null
3    ,  null
10   ,  null
TABLE2:
=======
rangeofids
2
3
9
10
11
12
13

我必须根据以下条件更新 TABLE1.tobeupdate（或发现它应该是值）：

1. 如果TABLE1.somid NOT exists in TABLE2.rangeofids，则预期结果为：待更新 = TABLE1.somid
2. 否则找到下一个可用（或未使用）的TABLE2.rangeofids，它比TABLE1.somid

所以期望值是：bu

TABLE1:
=======
somid, tobeupdated
1    ,  1
2    ,  4
3    ,  4
10   ,  14

我努力尝试，但我想出的最简单的解决方案是创建一个带有完整 id 序列（从 1 到 max(rangeofids)+1 ）的临时表，MINUS TABLE2.rangeofids这样我就可以找到MIN(TMPTABLE.id) where TMPTABLE.ID > TABLE1.somid。

但是没有更好的解决方案（没有临时表）吗？

注意：我无法创建过程/函数等，因此它必须是标准的（Oracle 10）SQL。

Answer 1

这是我

的尝试。

首先，我们应该仅使用 table2 来决定在找到值后应该返回什么值。

select rangeofids, 
  candidate, 
  nvl(candidate,lead(candidate ignore nulls) over (order by rangeofids)) as full_candidate
from (
    select rangeofids, case when dist=1 then null else rangeofids+1 end as candidate
    from (
        select rangeofids,
               lead(rangeofids) over (order by rangeofids) - rangeofids as dist
        from table2
        )
      );

在此之后，以下选择merge into table1 with将解决问题：

select someid, nvl(full_candidate, someid) 
from table1 a
left join (    
    --the above query
) b
on a.someid = b.rangeofids;

请参阅 SQLFIDDLE。