SQL递归查询范例

SQL递归查询实例
递归查询实例
http://justcramer.com/2010/05/30/scaling-threaded-comments-on-django-at-disqus/
递归查询实例 city 表
定义结构
    CREATE TABLE city(  id serial NOT NULL,  name character varying,  parent_id integer,  status boolean,  CONSTRAINT city_pkey PRIMARY KEY (id),  CONSTRAINT city_parent_id_fkey FOREIGN KEY (parent_id)      REFERENCES city (id) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION)WITH (  OIDS=FALSE);ALTER TABLE city  OWNER TO sys;        
插入数据
    INSERT INTO city (id, name, parent_id, status) VALUES (1, '广东', NULL, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (2, '湖南', NULL, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (3, '深圳', 1, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (4, '东莞', 1, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (5, '福田', 3, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (6, '南山', 3, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (7, '宝安', 3, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (8, '西乡', 7, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (9, '福永', 7, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (10, '龙华', 7, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (11, '长沙', 2, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (12, '湘潭', 2, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (13, '常德', 2, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (14, '桃源', 13, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (15, '汉寿', 13, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (16, '黑龙江', NULL, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (17, '伊春', 16, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (18, '哈尔滨', 16, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (19, '齐齐哈尔', 16, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (20, '牡丹江', 16, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (21, '佳木斯', 16, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (22, '民治', 10, NULL);INSERT INTO city (id, name, parent_id, status) VALUES (23, '上塘', 10, NULL);        
查询
    WITH RECURSIVE path(id, name, path, idpath, parent_id, status) AS (  SELECT id, name, '/' || name , '/' || id , parent_id, status FROM city WHERE parent_id is null  UNION  SELECT    city.id,    city.name,    parentpath.path ||      CASE parentpath.pathWHEN '/' THEN ''ELSE '/'      END || city.name,    parentpath.idpath ||     CASE parentpath.idpathWHEN '/' THEN ''ELSE '/'      END || city.id,    city.parent_id, city.status  FROM city, path as parentpath  WHERE city.parent_id = parentpath.id)SELECT * FROM path;        
结果输出
     id |   name   |           path            |    idpath    | parent_id | status----+----------+---------------------------+--------------+-----------+--------  1 | 广东     | /广东                     | /1           |           |  2 | 湖南     | /湖南                     | /2           |           | 16 | 黑龙江   | /黑龙江                   | /16          |           |  3 | 深圳     | /广东/深圳                | /1/3         |         1 |  4 | 东莞     | /广东/东莞                | /1/4         |         1 | 11 | 长沙     | /湖南/长沙                | /2/11        |         2 | 12 | 湘潭     | /湖南/湘潭                | /2/12        |         2 | 13 | 常德     | /湖南/常德                | /2/13        |         2 | 17 | 伊春     | /黑龙江/伊春              | /16/17       |        16 | 18 | 哈尔滨   | /黑龙江/哈尔滨            | /16/18       |        16 | 19 | 齐齐哈尔 | /黑龙江/齐齐哈尔          | /16/19       |        16 | 20 | 牡丹江   | /黑龙江/牡丹江            | /16/20       |        16 | 21 | 佳木斯   | /黑龙江/佳木斯            | /16/21       |        16 |  5 | 福田     | /广东/深圳/福田           | /1/3/5       |         3 |  6 | 南山     | /广东/深圳/南山           | /1/3/6       |         3 |  7 | 宝安     | /广东/深圳/宝安           | /1/3/7       |         3 | 14 | 桃源     | /湖南/常德/桃源           | /2/13/14     |        13 | 15 | 汉寿     | /湖南/常德/汉寿           | /2/13/15     |        13 |  8 | 西乡     | /广东/深圳/宝安/西乡      | /1/3/7/8     |         7 |  9 | 福永     | /广东/深圳/宝安/福永      | /1/3/7/9     |         7 | 10 | 龙华     | /广东/深圳/宝安/龙华      | /1/3/7/10    |         7 | 22 | 民治     | /广东/深圳/宝安/龙华/民治 | /1/3/7/10/22 |        10 | 23 | 上塘     | /广东/深圳/宝安/龙华/上塘 | /1/3/7/10/23 |        10 |(23 rows)    
?
?
?
?
    1 楼    MrLee23    昨天              你这个是mysql的吗？    2 楼    netkiller.github.com    昨天              postgresql 的    3 楼    MrLee23    昨天              呵呵，我对postgresql 不了解，但这个思想值得借鉴，看看mysql上是否也能做到类似的语句
SQL递归查询范例

热点推荐
