PostgreSQL·国家地区表的设计

netkiller old

发布于 2018-03-05 09:48:17

2K00

代码可运行

文章被收录于专栏：NetkillerNetkiller

运行总次数：0

代码可运行

本文节选自《Netkiller Architect 手札》

4.22. PostgreSQL 所特有数据库设计

4.22.1. 国家地区表的设计

 +-----------+
 | city      |
 |-----------|
 |id         | <---+
 |name       |     |
 |description|    1:n
 |status     |     |
 |parent_id  | o---+
 +-----------+

例 4.2. 递归查询实例 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.path
	WHEN '/' THEN ''
	ELSE '/'
      END || city.name,
    parentpath.idpath ||
     CASE parentpath.idpath
	WHEN '/' 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)