3階層構造で一番上の親を参照する

前提

以下のようなスキーマのテーブルを前提とする。 idが主キーで、parent_idはそのレコードの親のidを表す。parent_idが0の場合は、そのレコードが一番上の親という意味になる。この階層は最大3階層とする。

id(PK)parent_id
10
20
32
43
50
65

上のテーブルの階層構造は以下のようになる。

  • 1
  • 2
    • 3
      • 4
  • 5
    • 6

SQL

このテーブルについて、それぞれのレコードの一番上の親を参照するには以下のようにする。

 SELECT 
   COALESCE(
     CASE WHEN t3.parent_id = 0 THEN NULL ELSE t3.parent_id END, 
     CASE WHEN t2.parent_id = 0 THEN NULL ELSE t2.parent_id END, 
     CASE WHEN t1.parent_id = 0 THEN t1.id ELSE t1.parent_id END) AS top, 
     t1.id
 FROM 
   tbl t1
 LEFT OUTER JOIN
   tbl t2
 ON
   t1.parent_id = t2.id 
 LEFT OUTER JOIN
   tbl t3
 ON 
   t2.parent_id = t3.id
 ORDER BY top;

参考

PostgreSQL8.4以上なら再帰クエリを使うと良い。


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS

Last-modified: 2011-04-14 (木) 11:50:26