摘要：今天在壇子里有人提出了一個問題，問題是這樣的在以下指定表中中國遼寧山東沈陽大連濟南和平區沈河區現在給定一個號，想得到它完整的名字。遞歸往上找，直到為止。也就是最高層級時結束，求完整語句。

今天在壇子里有人提出了一個問題，問題是這樣的：在以下指定表中

id name fatherid
1 中國 0
2 遼寧 1
3 山東 1
4 沈陽 2
5 大連 2
6 濟南 3
7 和平區 4
8 沈河區 4

現在給定一個id號，想得到它完整的名字。如：
當id=7時，名字是：中國遼寧沈陽和平區
當id=5時，名字是：中國遼寧大連

id是任意給定的，不確定在哪一層。遞歸往上找，直到 fatherid=0 為止。也就是最高層級時結束，
求完整SQL語句。

看到這個問題，第一想到的是可以用 PG的遞歸查詢實現，之前也寫過類似的例子，
http://francs3.blog.163.com/b...，但之前的例子
是向下遞歸，而這里的需求是向上遞歸，略有不同，于是忍不住演示下：

這個問題的思路是分兩步走，第一步：查詢出指定節點的父節點；第二步：將查詢出的所有父節點排列到一行。

--1 創建測試表，并插入測試數據

skytf=> create table test_area(id int4,name varchar(32),fatherid int4);
CREATE TABLE

insert into test_area values (1, "中國"   ,0);
insert into test_area values (2, "遼寧"   ,1);
insert into test_area values (3, "山東"   ,1);
insert into test_area values (4, "沈陽"   ,2);
insert into test_area values (5, "大連"   ,2);
insert into test_area values (6, "濟南"   ,3);
insert into test_area values (7, "和平區" ,4);
insert into test_area values (8, "沈河區" ,4);
skytf=> select * From test_area;
 id |  name  | fatherid 
----+--------+----------
  1 | 中國   |        0
  2 | 遼寧   |        1
  3 | 山東   |        1
  4 | 沈陽   |        2
  5 | 大連   |        2
  6 | 濟南   |        3
  7 | 和平區 |        4
  8 | 沈河區 |        4
(8 rows)

--2 查詢指定節點以下的所有節點

  WITH RECURSIVE r AS ( 

       SELECT * FROM test_area WHERE id = 4 
     union   ALL 
       SELECT test_area.* FROM test_area, r WHERE test_area.fatherid = r.id 
     ) 
SELECT * FROM r ORDER BY id;
 id |  name  | fatherid 
----+--------+----------
  4 | 沈陽   |        2
  7 | 和平區 |        4
  8 | 沈河區 |        4
(3 rows)

備注：通常的用法是查詢指定節點以及指定節點以下的所有節點，那么本貼的需求剛好相反，需要查詢指定節點以上的所有節點。

--3 查詢指定節點以上的所有節點

 WITH RECURSIVE r AS ( 
       SELECT * FROM test_area WHERE id = 4 
     union   ALL 
       SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid 
     ) 
 SELECT * FROM r ORDER BY id;

 id | name | fatherid 
----+------+----------
  1 | 中國 |        0
  2 | 遼寧 |        1
  4 | 沈陽 |        2
(3 rows)

備注：這正是我們想要的結果，接下來需要將 name 字段結果集合并成一行，我這里想到的是創建個 function，當然也有其它方法。

--4 create funcion

CREATE or replace FUNCTION func_get_area(in in_id int4, out o_area text)  AS 

$$ 
DECLARE
   v_rec_record RECORD;
BEGIN

  o_area = "";
  FOR v_rec_record IN (WITH RECURSIVE r AS (SELECT *
                           FROM test_area
                          WHERE id = in_id
                         union ALL
                         SELECT test_area.*
                           FROM test_area, r
                          WHERE test_area.id = r.fatherid)SELECT name
                         FROM r
                        ORDER BY id) LOOP
    o_area := o_area || v_rec_record.name;
  END LOOP;
  return;
END; 
$$
LANGUAGE "plpgsql";

備注：函數的作用為拼接 name 字段。

--5 測試

 skytf=> select func_get_area(7) ;
   func_get_area    
--------------------
 中國遼寧沈陽和平區
(1 row)

skytf=> select func_get_area(5) ;
 func_get_area 
---------------
 中國遼寧大連
(1 row)

備注：正好實現了需求，當表數據量較大時，考慮到性能，建議在表 test_area 字段 id，fatherid 上建立多帶帶的索引。