项目中，经常会碰到多级的树形结构数据，如地区信息，省、市、区、街道等，或客户关系信息上三级，下三级等。
实际项目中，我们可能碰到以下两种需求：
- 一条记录中呈现路径：省 - 市 - 区 - 街道
- 一条记录中呈现上几级：上级，上上级，上上上级
此情此景，使用 WITH RECURSIVE 递归查询再合适不过。
递归查询
在介绍 WITH RECURSIVE 之前，我们先介绍一个 WITH
通过 WITH ，我们可以在写查询语句之前，为一个需要用到的子查询定义一个临时的别名，该别名可以查询语句中使用，从而简化查询语句。如下示例：
WITH 临时别名 AS(
SELECT 1 a, 2 b, 3 c
)
-- 下边是查询语句，像使用表或视图一样使用了“临时别名”
select a * a, b * b, c * c from 临时别名;
接下来我们再来看递归 RECURSIVE ，我们知道，递归形式上就是自己会调用自己，对于数据集而言，递归通常需要两个条件：
- 有入口数据，即递归开始的数据记录
- 有递归结束的条件，即在关联自己的同时有终止的条件
这第 2 点，原因很简单，关联自己产生的新数据记录又会成为递归关联中的数据。通常 WHERE 子句给出这个条件。
PostgreSQL 中，WITH RECURSIVE 即表示递归查询，“临时别名”在定义自身的子查询体中也可以使用。
我们来看一个示例：
-- 定义了一个临时别名 cet
WITH RECURSIVE cet AS (
-- 递归初始的数据，id=1, name="name 1", pid=null （最顶层父id为空）
SELECT 1 AS id, 'name 1' AS name, cast(null AS int) AS pid
UNION ALL
-- 联合 cet 自己，id 递增，结束条件为 id < 10。如果不加这个 WHERE 会如何？
SELECT id+1, 'name ' || (id+1), id FROM cet WHERE id < 10
)
SELECT * FROM cet;
输出以下结果：
搞定需求
上边的示例中的结果集就是一个典型的树形层级结构，我们以这个数据结果为例来完成文章开头提到的两个需求，首先我们把这个结果集变成一个视图以便当源数据用。
-- 创建一个名字为 view_tree_test 的视图
CREATE VIEW view_tree_test as
WITH RECURSIVE cet AS (
SELECT 1 AS id, 'name 1' AS name, cast(null AS int) AS pid
UNION ALL
SELECT id+1, 'name ' || (id+1), id FROM cet WHERE id < 10
)
SELECT * FROM cet;
我们以 view_tree_test 作为数据源来完成两大需求。
需求一，显示路径
WITH RECURSIVE tpath AS(
SELECT id, name, pid, name as path from view_tree_test where pid is null
UNION ALL
-- 联合子节点
SELECT a.id, a.name, a.pid,
tpath.path || '-' || a.name -- 父路径拼接当前节点名称形成路径
FROM view_tree_test a, tpath -- 关联已有结果，查询其子节点
WHERE a.pid = tpath.id
)
SELECT * FROM tpath;
查询结果可见其效果：
需求二，显示每第记录的上三级
WITH RECURSIVE uuup AS(
-- 初始值，顶层的上级均置为空，需要几级置几个空
SELECT id, name, '' 上级, '' 上上级, '' 上上上级 from view_tree_test where pid is null
UNION ALL
-- 联合子节点
SELECT a.id, a.name,
-- 父级即为上级，父的上级为上上级
uuup.name 上级, uuup.上级 上上级, uuup.上上级 上上上级
FROM view_tree_test a, uuup -- 关联已有结果，查询其子节点
WHERE a.pid = uuup.id
)
SELECT * FROM uuup;
顺利呈现相关的上三级，需要更多上级也是 SO EASY！
小结
PostgreSQL 提供了相当多实用的数据处理方式，让数据处理起来很是便捷。树形结构是应用中经常会使用到，使用递归查询能方便的处理跨层级的计算，还有很多想象空间哦。
