5-20 階層のデータを出力

SQLパズル

カテゴリーテーブル
ID   親ID   name
--   ----   -------
 1   null   ルート1
 2   null   ルート2
 3      1       枝1
 4      1       枝2
 5      3       葉1
 6      3       葉2
 7      2       枝3
 8      7       葉3

エントリーテーブル
ID  postedOn
--  ----------
 1  2002-05-15
 2  2003-03-16
 3  2003-06-01
 4  2003-06-19
 5  2004-01-20
 6  2004-03-12

木構造を持っているカテゴリテーブルと
エントリー.ID = カテゴリー.ID なエントリーテーブルがあり、

カテゴリーテーブルのルート1以下に所属する、
エントリーテーブルのレコードを出力する。

出力結果
ID  postedOn
--  ----------
 1  2002-05-15
 3  2003-06-01
 4  2003-06-19
 5  2004-01-20
 6  2004-03-12

データ作成スクリプト

create table カテゴリー(
ID   number(1),
親ID number(1),
name varchar2(7));

create table エントリー(
ID       number(1),
postedOn date);

insert into カテゴリー(ID,親ID,name) values(1,NULL,'ルート1');
insert into カテゴリー(ID,親ID,name) values(2,NULL,'ルート2');
insert into カテゴリー(ID,親ID,name) values(3,1,'枝1');
insert into カテゴリー(ID,親ID,name) values(4,1,'枝2');
insert into カテゴリー(ID,親ID,name) values(5,3,'葉1');
insert into カテゴリー(ID,親ID,name) values(6,3,'葉2');
insert into カテゴリー(ID,親ID,name) values(7,2,'枝3');
insert into カテゴリー(ID,親ID,name) values(8,7,'葉3');
insert into エントリー(ID,postedOn) values(1,'2002-05-15');
insert into エントリー(ID,postedOn) values(2,'2003-03-16');
insert into エントリー(ID,postedOn) values(3,'2003-06-01');
insert into エントリー(ID,postedOn) values(4,'2003-06-19');
insert into エントリー(ID,postedOn) values(5,'2004-01-20');
insert into エントリー(ID,postedOn) values(6,'2004-03-12');
commit;

SQL

--■■■in述語を使う方法■■■
select ID,to_char(postedOn,'YYYY/MM/DD') as postedOn
  from エントリー a
 where ID in (select b.ID from カテゴリー b
               start with b.ID = (select c.ID from カテゴリー c
                                   where c.name = 'ルート1')
               connect by prior b.ID = b.親ID)
order by ID;

--■■■exists述語を使う方法■■■
select ID,to_char(postedOn,'YYYY/MM/DD') as postedOn
  from エントリー a
where exists(select 1 from カテゴリー b
              where b.ID = a.ID
              start with b.ID = (select c.ID from カテゴリー c
                                  where c.name = 'ルート1')
              connect by prior b.ID = b.親ID)
order by ID;

解説

階層問い合わせを使うと、データを探索できます。

SQLの抽出結果を階層構造で表示するテクニック − ＠IT

---