postgresql 的遞歸查詢功能很強(qiáng)大，可以實(shí)現(xiàn)傳統(tǒng) sql 無法實(shí)現(xiàn)的事情。那遞歸查詢的執(zhí)行邏輯是什么呢？在遞歸查詢中，我們一般會用到 union 或者 union all，他們兩者之間的區(qū)別是什么呢？

遞歸查詢的執(zhí)行邏輯

遞歸查詢的基本語法如下

WITH RECURSIVE ctename AS (SELECT /* non-recursive branch, cannot reference "ctename" */UNION [ALL]SELECT /* recursive branch referencing "ctename" */
)
SELECT ...
FROM ctename ...

其本身也是一個CTE，可以將復(fù)雜的查詢邏輯進(jìn)行分離，讓整個查詢的邏輯更加清晰。對于遞歸查詢而言，分為兩部分：

1. 非遞歸部分。即例子中的 UNION [ALL] 的上半部分
2. 遞歸部分。即例子中的 UNION [ALL] 的下半部分

遞歸查詢的邏輯如下：

1. 計(jì)算非遞歸部分，其結(jié)果將作為遞歸查詢的數(shù)據(jù)集，也是初始數(shù)據(jù)集
2. 在第一步計(jì)算出來的數(shù)據(jù)上，執(zhí)行遞歸部分，新查詢出的數(shù)據(jù)將作為下次遞歸執(zhí)行的數(shù)據(jù)集。也就是說，每次遞歸使用的數(shù)據(jù)集都是上次遞歸的結(jié)果
3. 直到?jīng)]有新的數(shù)據(jù)產(chǎn)生后，遞歸結(jié)束
4. 將每一次遞歸的數(shù)據(jù)進(jìn)行聚合，就拿到了最終的數(shù)據(jù)集

UNION 和 UNION ALL

1. UNION: 會將本次遞歸查詢到的數(shù)據(jù)進(jìn)行內(nèi)部去重，也會和之前遞歸查詢出的數(shù)據(jù)進(jìn)行去重
2. UNION ALL: 不會對數(shù)據(jù)進(jìn)行去重

舉個例子

// 創(chuàng)建表
create table document_directories
(id         bigserial                                          not null,name       text                                               not null,created_at timestamp with time zone default CURRENT_TIMESTAMP not null,updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,parent_id  bigint                   default 0                 not null
);// 插入示例數(shù)據(jù)，有兩條數(shù)據(jù)是一樣的
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (1, '中國', '2020-03-28 15:55:27.137439', '2020-03-28 15:55:27.137439', 0);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (2, '上海', '2020-03-28 15:55:40.894773', '2020-03-28 15:55:40.894773', 1);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (3, '北京', '2020-03-28 15:55:53.631493', '2020-03-28 15:55:53.631493', 1);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (4, '南京', '2020-03-28 15:56:05.496985', '2020-03-28 15:56:05.496985', 1);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (5, '浦東新區(qū)', '2020-03-28 15:56:24.824672', '2020-03-28 15:56:24.824672', 2);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (6, '徐匯區(qū)', '2020-03-28 15:56:39.664924', '2020-03-28 15:56:39.664924', 2);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (6, '徐匯區(qū)', '2020-03-28 15:56:39.664924', '2020-03-28 15:56:39.664924', 2);

使用 UNION ALL 進(jìn)行數(shù)據(jù)查詢

with recursive sub_shanghai as (select id, name, parent_idfrom document_directorieswhere id=2union allselect dd.id, dd.name, dd.parent_idfrom document_directories ddjoin sub_shanghai on dd.parent_id=sub_shanghai.id)select * from sub_shanghai;

結(jié)果如下

?

使用 UNION 進(jìn)行查詢

with recursive sub_shanghai as (select id, name, parent_idfrom document_directorieswhere id=2unionselect dd.id, dd.name, dd.parent_idfrom document_directories ddjoin sub_shanghai on dd.parent_id=sub_shanghai.id)select * from sub_shanghai;

得到結(jié)果如下

?

我們修改下原始數(shù)據(jù)，再看下去重邏輯的區(qū)別

update document_directories set parent_id = 2 where id=2;

當(dāng)我們使用 UNION 進(jìn)行遞歸查詢時，結(jié)果并沒有發(fā)生變化。但是當(dāng)我們使用 UNION ALL 進(jìn)行查詢時，會一直執(zhí)行。這是因?yàn)?UNION ALL 不會將數(shù)據(jù)進(jìn)行去重，而每次遞歸查詢的時候，總歸能查詢到 {"id": 5, name:"上海", "parent_id": 2} 這條數(shù)據(jù)，所以遞歸就沒有終止條件。

從而也驗(yàn)證了，UNION 不但會將本次遞歸查詢的數(shù)據(jù)進(jìn)行內(nèi)部去重，也會和之前的遞歸結(jié)果進(jìn)行去重。