SELECT递归CTE

参考文献[2]展示了SQL Server中的一个自底向上的递归例子，我们稍微改动一下，在PostgreSQL中测试一下。

准备测试数据：

CREATE TABLE tb(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(10))
INSERT INTO tb VALUES('1', NULL, '广东省')
INSERT INTO tb VALUES('2', '1', '广州市')
INSERT INTO tb VALUES('3', '1', '深圳市')
INSERT INTO tb VALUES('4', '2', '天河区')
INSERT INTO tb VALUES('5', '3', '罗湖区')
INSERT INTO tb VALUES('6', '3', '福田区')
INSERT INTO tb VALUES('7', '3', '宝安区')
INSERT INTO tb VALUES('8', '7', '西乡镇')
INSERT INTO tb VALUES('9', '7', '龙华镇')
 
postgres=# SELECT * FROM tb ORDER BY id;
 id | pid |  name
----+-----+--------
 1  |     | 广东省
 2  | 1   | 广州市
 3  | 1   | 深圳市
 4  | 2   | 天河区
 5  | 3   | 罗湖区
 6  | 3   | 福田区
 7  | 3   | 宝安区
 8  | 7   | 西乡镇
 9  | 7   | 龙华镇
(9 ROWS)

其中id是，每个地区的编号，pid是上级地区的id，例如，“天河区”的pid=2，所以“天河区”的上级地区是“广州市（id=2）”。

现在要遍历出每个地区及其所有的下级地区（包括下级的下级……），SQL如下：

snippet.sql

WITH RECURSIVE cte(id, pid, NAME) AS
(SELECT id,
        pid,
        name 
  FROM  tb a
  WHERE a.pid IS NOT NULL
  UNION ALL
  SELECT b.id,
         a.pid,
         b.NAME
  FROM tb a
       INNER JOIN cte b ON a.id = b.pid
  WHERE a.pid IS NOT NULL
)
SELECT pid AS id, id AS SID, NAME
FROM cte a
UNION
SELECT id, id, name FROM tb
ORDER BY id, sid;

结果集：

snippet.sql

 id | sid |  name
----+-----+--------
 1  | 1   | 广东省
 1  | 2   | 广州市
 1  | 3   | 深圳市
 1  | 4   | 天河区
 1  | 5   | 罗湖区
 1  | 6   | 福田区
 1  | 7   | 宝安区
 1  | 8   | 西乡镇
 1  | 9   | 龙华镇
 2  | 2   | 广州市
 2  | 4   | 天河区
 3  | 3   | 深圳市
 3  | 5   | 罗湖区
 3  | 6   | 福田区
 3  | 7   | 宝安区
 3  | 8   | 西乡镇
 3  | 9   | 龙华镇
 4  | 4   | 天河区
 5  | 5   | 罗湖区
 6  | 6   | 福田区
 7  | 7   | 宝安区
 7  | 8   | 西乡镇
 7  | 9   | 龙华镇
 8  | 8   | 西乡镇
 9  | 9   | 龙华镇
(25 ROWS)

上述结果集，可以得到每个地区的所有子级（sid）地区，包括子级的子级。

递归UPDATE

现在有这样一个需求：表t中存储了一棵个树形数据结构，id是每个节点的编号，pid是父级节点的编号，rate是个百分比，是当前层级中的百分比。现在要求通过UPDATE更新t表，将rate折算成总体的百分比，即需要逐级向上计算，以折算百分比，数据如下：

snippet.sql

CREATE TABLE t (id INT, pid INT, rate INT);
INSERT INTO t VALUES (1000, 0, 8);
INSERT INTO t VALUES (1001, 0, 9);
INSERT INTO t VALUES (1002, 0, 40);
INSERT INTO t VALUES (2000, 1002, 60);
INSERT INTO t VALUES (2001, 1002, 10);
INSERT INTO t VALUES (2002, 1002, 30);
INSERT INTO t VALUES (3000, 2002, 60);
INSERT INTO t VALUES (3001, 2002, 20);

例如，id=3001的数据行rate应该UPDATE为： $$ 20 * 30 * 40 / 100 / 100 = 2.4 ≈ 2 $$ 在UPDATE数据前，我们先用SELECT计算每行的新值：

snippet.sql

postgres=# 
WITH RECURSIVE cte(id, pid, rate) AS
(SELECT id,
        pid,
        rate
  FROM  t a
  UNION ALL
  SELECT cte.id,
         b.pid,
         b.rate * cte.rate / 100
  FROM t b
  INNER JOIN cte ON b.id = cte.pid
)
SELECT id, rate FROM cte WHERE pid = 0 ORDER BY id;
 
  id  | rate
------+------
 1000 |    8
 1001 |    9
 1002 |   40
 2000 |   24
 2001 |    4
 2002 |   12
 3000 |    7
 3001 |    2
(8 ROWS)

再代入UPDATE语句里：

snippet.sql

UPDATE t SET rate = NEW.rate
FROM
(
  WITH RECURSIVE cte(id, pid, rate) AS
  (SELECT id,
          pid,
          rate 
    FROM  t a
    UNION ALL
    SELECT cte.id,
           b.pid,
           b.rate * cte.rate / 100
    FROM t b
    INNER JOIN cte ON b.id = cte.pid
)
SELECT id, rate FROM cte WHERE pid = 0
) NEW
WHERE NEW.id = t.id;

看一下UPDATE后的最终结果：

snippet.sql

postgres=# SELECT * FROM t ORDER BY id;
  id  | pid  | rate
------+------+------
 1000 |    0 |    8
 1001 |    0 |    9
 1002 |    0 |   40
 2000 | 1002 |   24
 2001 | 1002 |    4
 2002 | 1002 |   12
 3000 | 2002 |    7
 3001 | 2002 |    2
(8 ROWS)