原来SQL也能递归

1. WITH

本文还是基于 Postgres13 演示，在开始讲解递归前首先得介绍下 WITH，递归是以它为前提的

WITH 语句也叫 CTE 公用表达式，跟我们的临时表差不多（轻量级视图），辅助语句，它可以自引用(最突出的地方就是用作递归)，可以多次引用，实现重复利用。

在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

语法示例

WITH cte_name(column_name...) AS (
	inner_query_definition_1
), cte_name2(column_name...) AS (
	inner_query_definition_2
	)

outer_query_definition

column_name 指定 inner_query_definition_1 的列名（从前往后），可以省略在内部定义，必须保证唯一
inner_query_definition_1 就是辅助语句，可以是 SELECT 可以是 INSERT 等等
outer_query_definition 主语句，必须和CTE定义语句同时执行，CTE 是临时虚拟表，只有立即引用它，才是有意义的。

1.1 初始化数据

CREATE TABLE area
(
    id        serial
        CONSTRAINT area_pk
            PRIMARY KEY,
    parent_id integer,
    name      varchar(20)
);

COMMENT ON TABLE area IS '地区';

COMMENT ON COLUMN area.parent_id IS '父id';

COMMENT ON COLUMN area.name IS '名称';

ALTER TABLE area
    OWNER TO postgres;

-- 中国
INSERT INTO area(name, parent_id)
VALUES ('中国', 0),
       ('北京', 2),
       ('上海', 2),
       ('广东', 2),
       ('湖北', 2);

-- 湖北城市
INSERT INTO area(name, parent_id)
VALUES ('武汉市', 6),
       ('黄石市', 6),
       ('襄樊市', 6),
       ('十堰市', 6),
       ('荆州市', 6),
       ('宜昌市', 6),
       ('荆门市', 6),
       ('鄂州市', 6),
       ('孝感市', 6),
       ('黄冈市', 6),
       ('咸宁市', 6),
       ('随州市', 6),
       ('恩施土家族苗族自治州', 6),
       ('湖北省直辖行政单位', 6),
       ('仙桃市', 6),
       ('神农架林区', 6);

-- 武汉地区
INSERT INTO area(name, parent_id)
VALUES ('江岸区', 7),
       ('江汉区', 7),
       ('硚口区', 7),
       ('汉阳区', 7),
       ('武昌区', 7),
       ('青山区', 7),
       ('洪山区', 7),
       ('东西湖区', 7),
       ('汉南区', 7),
       ('蔡甸区', 7);

-- 黄冈地区
INSERT INTO area(name, parent_id)
VALUES ('黄州区', 16),
       ('麻城市', 16),
       ('武穴市', 16),
       ('红安县', 16),
       ('罗田县', 16),
       ('英山县', 16),
       ('浠水县', 16),
       ('蕲春县', 16),
       ('黄梅县', 16),
       ('团风县', 16);

-- 黄石地区
INSERT INTO area(name, parent_id)
VALUES ('黄石港区', 8),
       ('西塞山区', 8),
       ('下陆区', 8),
       ('铁山区', 8),
       ('大冶市', 8),
       ('阳新县', 8);

-- 襄樊地区
INSERT INTO area(name, parent_id)
VALUES ('襄城区', 9),
       ('樊城区', 9),
       ('襄阳区', 9),
       ('老河口市', 9),
       ('枣阳市', 9),
       ('宜城市', 9),
       ('南漳县', 9),
       ('谷城县', 9),
       ('保康县', 9);


-- 十堰地区
INSERT INTO area(name, parent_id)
VALUES ('张湾区', 10),
       ('茅箭区', 10),
       ('丹江口市', 10),
       ('郧县', 10),
       ('竹山县', 10),
       ('房县', 10),
       ('郧西县', 10),
       ('竹溪县', 10);


-- 广东城市
INSERT INTO area(name, parent_id)
VALUES ('广州市', 5),
       ('深圳市', 5),
       ('珠海市', 5),
       ('汕头市', 5),
       ('韶关市', 5),
       ('河源市', 5),
       ('梅州市', 5),
       ('惠州市', 5),
       ('汕尾市', 5),
       ('东莞市', 5);

-- 广州
INSERT INTO area(name, parent_id)
VALUES ('越秀区', 66),
       ('东山区', 66),
       ('荔湾区', 66),
       ('海珠区', 66),
       ('天河区', 66),
       ('芳村区', 66),
       ('白云区', 66),
       ('黄埔区', 66),
       ('番禺区', 66),
       ('花都区', 66);

-- 深圳
INSERT INTO area(name, parent_id)
VALUES ('罗湖区', 67),
       ('南山区', 67),
       ('宝安区', 67),
       ('龙岗区', 67),
       ('盐田区', 67),
       ('福田区', 67);

-- 上海城市
INSERT INTO area(name, parent_id)
VALUES ('黄浦区', 4),
       ('卢湾区', 4),
       ('徐汇区', 4),
       ('长宁区', 4),
       ('静安区', 4),
       ('普陀区', 4),
       ('闸北区', 4),
       ('虹口区', 4),
       ('杨浦区', 4),
       ('宝山区', 4),
       ('闵行区', 4),
       ('嘉定区', 4),
       ('浦东新区', 4),
       ('松江区', 4),
       ('金山区', 4),
       ('青浦区', 4),
       ('南汇区', 4),
       ('奉贤区', 4),
       ('崇明县', 4);

-- 北京城市
INSERT INTO area(name, parent_id)
VALUES ('东城区', 3),
       ('西城区', 3),
       ('崇文区', 3),
       ('宣武区', 3),
       ('朝阳区', 3),
       ('丰台区', 3),
       ('石景山区', 3),
       ('海淀区', 3),
       ('门头沟区', 3),
       ('房山区', 3),
       ('通州区', 3),
       ('顺义区', 3),
       ('昌平区', 3),
       ('大兴区', 3),
       ('怀柔区', 3),
       ('平谷区', 3),
       ('延庆县', 3),
       ('密云县', 3);

parent_id 也许不相同，所以需要注意下数据是否准确

1.2 示例

1.2.1 SELECT

# 定义CTE 必须被一条语句引用，可以多次引用但是不能在多条语句引用
WITH cte_month AS (
    SELECT GENERATE_SERIES(1, 12) AS month #内部定义别名（AS是必须的）
    # 可以使用 ORDEY BY 语句（也许别的数据库不行）
)
SELECT *
FROM cte_month;

这边我们使用 GENERATE_SERIES 函数来进行一定规则的填充模拟月份。需要排序或者多次引用的可以自己尝试，比如自连接

1.2.2 UPDATE

# 外部定义列名（顺序）
WITH cte_area(id, 修改后城市名, 父id) AS (
    UPDATE area SET name = 'test' WHERE name = '密云县' AND parent_id = 3
        RETURNING * #返回数据（可返回具体列）
)

SELECT *
FROM cte_area;

我们可以看到返回的是修改后的数据，需要注意的是 cte 定义的列，必须在结果中返回，否则报错

1.2.3 DELETE

WITH cte_area AS (
    DELETE FROM area WHERE name = '密云县' AND parent_id = 3
        RETURNING *
)
INSERT
INTO area
SELECT *
FROM cte_area;

# 查询一下数据看看
SELECT *
FROM area
WHERE name = '密云县'
  AND parent_id = 3;

删除后会返回删除的数据，然后我们再去新增哈哈（只是举例子）

1.2.4 INSERT

WITH cte_area AS (
    INSERT INTO area VALUES (129, 'test', 1)
        RETURNING *
)
SELECT *
FROM cte_area;

返回的就是新增的数据。

WITH 辅助语句和主语句都可以是SELECT，INSERT，UPDATE，DELETE中的任何一种语句，除了演示的这几种，还有很多组合，例如定义多个辅助子句，我相信平常工作肯定会有能用得上的场景，自行发挥。

2. Recursive

recursive 是一个 WITH 的一个重要属性，使用 recursive 可以引用自己的输出，就能达到递归的效果了

语法

WITH RECURSIVE cte_name AS (
    非递归部分
    [union | union all]
    递归部分
)
SELECT *
FROM cte_name;

开始的时候我们初始化了一个地区表，现在我们来看下湖北下面有多少城市

WITH RECURSIVE res AS (
    SELECT id, name, parent_id
    FROM area
    WHERE parent_id = 6 -- id为6的是湖北
    UNION ALL
    SELECT child.id, child.name, child.parent_id
    FROM res
             JOIN area child ON res.id = child. 
)
SELECT *
FROM res;

没截完，可以看到查出了湖北有哪些城市，以及城市下面又有哪些城市，这种结构只能靠递归去做，我们不知道下面有多少级。

解释一下上面 sql 的流程，首先是 UNION ALL 上面一部分，刚也说了这个是非递归部分，也就是说只会查一次，是我们的基础数据，把结果集放入 res ，然后

UNION ALL 是用来合并我们多个查询结果集的，稍后再讲解，最后就是下面一部分引用了我们的 res 结果集，然后去关联查，找到相匹配的下级数据，跟之前的 res 合并，一直重复，直到没有匹配的，最后递归结束。

非常适合组织结构，树状结构等等，很完美，需要注意的是跟在代码里面一样不能产生环状，停不下来

UNION ALL 和 UNION 的异同

都是合并多个结果集的
UNION ALL 不去重不排序，UNION 去重排序
UNION ALL 效率更高

上面是从上往下查，再来一个从下往上查，看一下39浠水县所属上层地区

WITH RECURSIVE res AS (
    SELECT id, name::text, parent_id
    FROM area
    WHERE id = 39
    UNION ALL
    SELECT parent.id, CONCAT(res.name, '->', parent.name), parent.parent_id
    FROM res
             JOIN area parent ON res.parent_id = parent.id
)
SELECT *
FROM res;

-- 拼接字符串后必须要强转为text，不然会报错

格式化了一下，最终效果没毛病，使用递归时也有需要注意的地方，语法有要求，一定要尝试才会知道！

SQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

设计模式->单例模式上一篇

RequestScope 注解的妙用下一篇