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

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

id name fatherid
1 中國(guó) 0
2 遼寧 1
3 山東 1
4 沈陽 2
5 大連 2
6 濟(jì)南 3
7 和平區(qū) 4
8 沈河區(qū) 4

現(xiàn)在給定一個(gè)id號(hào)，想得到它完整的名字。如：
當(dāng)id=7時(shí)，名字是：中國(guó)遼寧沈陽和平區(qū)
當(dāng)id=5時(shí)，名字是：中國(guó)遼寧大連

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

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

這個(gè)問題的思路是分兩步走，第一步：查詢出指定節(jié)點(diǎn)的父節(jié)點(diǎn)；第二步：將查詢出的所有父節(jié)點(diǎn)排列到一行。

--1 創(chuàng)建測(cè)試表，并插入測(cè)試數(shù)據(jù)

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

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

--2 查詢指定節(jié)點(diǎn)以下的所有節(jié)點(diǎn)

  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 | 和平區(qū) |        4
  8 | 沈河區(qū) |        4
(3 rows)

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

--3 查詢指定節(jié)點(diǎn)以上的所有節(jié)點(diǎn)

 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 | 中國(guó) |        0
  2 | 遼寧 |        1
  4 | 沈陽 |        2
(3 rows)

備注：這正是我們想要的結(jié)果，接下來需要將 name 字段結(jié)果集合并成一行，我這里想到的是創(chuàng)建個(gè) function，當(dāng)然也有其它方法。

--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";

備注：函數(shù)的作用為拼接 name 字段。

--5 測(cè)試

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

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

備注：正好實(shí)現(xiàn)了需求，當(dāng)表數(shù)據(jù)量較大時(shí)，考慮到性能，建議在表 test_area 字段 id，fatherid 上建立多帶帶的索引。