7-56 木の中の最大値を取得

SQLパズル

掲示板テーブル
ID  返信元ID  タイトル       投稿日
--  --------  ----------    ----------
 1      null  SQL           2000/12/12
 2         1  Re SQL        2000/12/13
 3         2  Re SQL        2000/12/14
 4         3  亀レス        2005/12/20
 5         1  Re [2]:SQL    2000/12/15
10      null  教えて        2000/12/16
20      null  Oracle        2000/10/01
21        20  Re Oracle     2000/12/01
22        20  亀レス        2005/12/31

掲示板テーブルから、スレッド内での投稿日の最大値(最新投稿日)を出力する。

出力結果
ID  返信元ID  タイトル       投稿日        最新投稿日
--  --------  ----------    ----------   ----------
 1      null  SQL           2000/12/12   2005/12/20
 2         1  Re SQL        2000/12/13   2005/12/20
 3         2  Re SQL        2000/12/14   2005/12/20
 4         3  亀レス        2005/12/20   2005/12/20
 5         1  Re [2]:SQL    2000/12/15   2005/12/20
10      null  教えて        2000/12/16   2004/12/16
20      null  Oracle        2000/10/01   2005/12/31
21        20  Re Oracle     2000/12/01   2005/12/31
22        20  亀レス        2005/12/31   2005/12/31

データ作成スクリプト

create table 掲示板(
ID       number(2),
返信元ID number(2),
タイトル varchar2(10),
投稿日   date);

insert into 掲示板 values( 1,null,'SQL'       ,to_date('2000/12/12','YYYY/MM/DD'));
insert into 掲示板 values( 2,   1,'Re SQL'    ,to_date('2000/12/13','YYYY/MM/DD'));
insert into 掲示板 values( 3,   2,'Re SQL'    ,to_date('2000/12/14','YYYY/MM/DD'));
insert into 掲示板 values( 4,   3,'亀レス'    ,to_date('2005/12/20','YYYY/MM/DD'));
insert into 掲示板 values( 5,   1,'Re [2]:SQL',to_date('2000/12/15','YYYY/MM/DD'));
insert into 掲示板 values(10,null,'教えて'    ,to_date('2000/12/16','YYYY/MM/DD'));
insert into 掲示板 values(20,null,'Oracle'    ,to_date('2000/10/01','YYYY/MM/DD'));
insert into 掲示板 values(21,  20,'Re Oracle' ,to_date('2000/12/01','YYYY/MM/DD'));
insert into 掲示板 values(22,  20,'亀レス'    ,to_date('2005/12/31','YYYY/MM/DD'));
commit;

SQL

col 投稿日     for a20
col 最新投稿日 for a20

--■■■Last_Value関数を使う方法■■■
select ID,返信元ID,タイトル,to_char(投稿日,'YYYY/MM/DD') as 投稿日,
(select to_char(max(b.投稿日),'YYYY/MM/DD')
   from 掲示板 b
 Start With b.RowID = (select distinct Last_Value(c.RowID) over(order by Level
                       Rows between Unbounded Preceding and Unbounded Following)
                       from 掲示板 c
                       Start With c.RowID = a.RowID
                       connect by Prior c.返信元ID = c.ID)
connect by Prior b.ID = b.返信元ID) as 最新投稿日
from 掲示板 a;

--■■■Connect_by_IsLeafを使う方法(10g以降)■■■
select ID,返信元ID,タイトル,to_char(投稿日,'YYYY/MM/DD') as 投稿日,
(select to_char(max(b.投稿日),'YYYY/MM/DD')
   from 掲示板 b
 Start With b.RowID = (select c.RowID
                       from 掲示板 c
                       where Connect_by_IsLeaf = 1
                       Start With c.RowID = a.RowID
                       connect by Prior c.返信元ID = c.ID)
connect by Prior b.ID = b.返信元ID) as 最新投稿日
from 掲示板 a;

--■■■connect_by_rootを使う方法(10g以降)■■■
select ID,返信元ID,タイトル,
to_char(投稿日,'YYYY/MM/DD') as 投稿日,
to_char(max(投稿日) over(partition by PID),'YYYY/MM/DD') as 最新投稿日
from (select ID,返信元ID,タイトル,投稿日,
      connect_by_root ID as PID
        from 掲示板
      start with 返信元ID is null
      connect by Prior ID = 返信元ID);

解説

階層問い合わせで、木の根を取得してから、
階層問い合わせを行い、投稿日の最大値を取得してます。

---