10-148 前後のn件の行も出力

SQLパズル

hogeTable
ID   name
--  -----
 2  1番目
 4  2番目
 6  3番目
 8  4番目
10  5番目

IDを指定して、
そのIDの行と、その前後(IDの昇順)の行も出力する

出力結果

指定IDが2なら
ID   name
--  -----
 2  1番目
 4  2番目

指定IDが6なら
ID   name
--  -----
 4  2番目
 6  3番目
 8  4番目

指定IDが10なら
ID   name
--  -----
 8  4番目
10  5番目

データ作成スクリプト

create table hogeTable(
ID   number(2) primary key,
name varchar2(5));

begin
    for i in 1..5 Loop
        insert into hogeTable values(i*2,to_char(i) || '番目');
            commit;
    end Loop;
end;
/

SQL

def inID = 2

--■■■相関サブクエリを使う方法1■■■
select ID,name
  from hogeTable
 where ID in((select max(ID) from hogeTable where ID < &inID),
             &inID,
             (select min(ID) from hogeTable where ID > &inID))
order by ID;

--■■■相関サブクエリを使う方法2■■■
select ID,name
  from hogeTable
 where ID >= nvl((select max(ID) from hogeTable where ID < &inID),ID)
   and ID <= nvl((select min(ID) from hogeTable where ID > &inID),ID)
order by ID;

--■■■Row_Number関数を使う方法■■■
select ID,Name
from (select ID,name,Row_Number() over(order by ID) as Rank,
      sum(case when &inID >= ID then 1 else 0 end) over() as inIDRank
        from hogeTable)
 where abs(inIDRank-Rank) <= 1;

--■■■Lag関数とLead関数を使う方法(CountStopなし)■■■
select ID,name
  from (select ID,name,
        Lag(id) over(order by id)  as LagID,
        Lead(id) over(order by id) as LeadID
          from hogeTable)
 where &inID in (ID,LagID,LeadID);

--■■■Lag関数とLead関数を使う方法(CountStopあり)■■■
select ID,name
  from (select ID,name,
        Lag(id)  over(order by id)  as LagID,
        Lead(id) over(order by id) as LeadID
          from hogeTable)
 where &inID in (ID,LagID,LeadID)
   and RowNum <= 3;

解説

SQLの結果が最大でも3件だと、事前に分かるので
パフォーマンスが要求される場合は、
RowNum <= 3
として、CountStopを発生させてもいいでしょう

RowNum指定によるCountStopが効果的なのは、
フルスキャンを途中で止めれる時のようです

10-3 RowNum指定によるCountStop

類似のSQLとして5-53 前後の行も出力があります