3-31 指定件数と打ち切りフラグを出力

SQLパズル

Table1    Table2
Val       Val
---       ---
 10         5
 20        15
 30        25
 40        35
 50        45
 60        55

Table1と、Table2の和集合をValの降順に5レコードのみ出力する。
6レコード以上あって、5レコードに打ち切ったかを示すIsCutFlagも出力する。

出力結果
Val  IsCutFlag
---  ---------
 60          1
 55          1
 50          1
 45          1
 40          1
 35          1

データ作成スクリプト

create table Table1(Val number(2));
create table Table2(Val number(2));

insert all
into Table1 values(10)
into Table1 values(20)
into Table1 values(30)
into Table1 values(40)
into Table1 values(50)
into Table1 values(60)
into Table2 values( 5)
into Table2 values(15)
into Table2 values(25)
into Table2 values(35)
into Table2 values(45)
into Table2 values(55)
select 1 from dual;
commit;

SQL

--■■■Row_Numberを使う方法1■■■
select Val,IsCutFlag
 from (select Val,
       Row_Number() over(order by Val desc) as Rank,
       max(decode(RowNum,5+1,1,0)) over() as IsCutFlag
         from (select Val
                 from Table1
               union all
               select Val
                 from Table2))
where Rank <= 5;

--■■■Row_Numberを使う方法2■■■
select Val,IsCutFlag
  from (select Val,
        Row_Number() over(order by Val desc) as Rank,
        case when count(*) over() > 5
             then 1 else 0 end as IsCutFlag
          from (select Val
                  from Table1
                union all
                select Val
                  from Table2))
  where Rank <= 5;

--■■■インラインビューでのorder byを使う方法■■■
select Val,IsCutFlag
  from (select Val,
        RowNum as Rank,
        case when count(*) over() > 5
             then 1 else 0 end as IsCutFlag
          from (select Val
                  from Table1
                union all
                select Val
                  from Table2
                order by Val desc)
       where RowNum <= 5+1)
where Rank <= 5;

解説

Row_Numberを使う方法2が
最も可読性が高そうですね

---