带网站的图片素材,网站建站备案,网络科技有限公司注册资金最低,域名购买之后怎么做网站postgresql 的递归查询功能很强大#xff0c;可以实现传统 sql 无法实现的事情。那递归查询的执行逻辑是什么呢#xff1f;在递归查询中#xff0c;我们一般会用到 union 或者 union all#xff0c;他们两者之间的区别是什么呢#xff1f;
递归查询的执行逻辑
递归查询的…postgresql 的递归查询功能很强大可以实现传统 sql 无法实现的事情。那递归查询的执行逻辑是什么呢在递归查询中我们一般会用到 union 或者 union all他们两者之间的区别是什么呢
递归查询的执行逻辑
递归查询的基本语法如下
WITH RECURSIVE ctename AS (SELECT /* non-recursive branch, cannot reference ctename */UNION [ALL]SELECT /* recursive branch referencing ctename */
)
SELECT ...
FROM ctename ...
其本身也是一个CTE可以将复杂的查询逻辑进行分离让整个查询的逻辑更加清晰。对于递归查询而言分为两部分
非递归部分。即例子中的 UNION [ALL] 的上半部分递归部分。即例子中的 UNION [ALL] 的下半部分
递归查询的逻辑如下
计算非递归部分其结果将作为递归查询的数据集也是初始数据集在第一步计算出来的数据上执行递归部分新查询出的数据将作为下次递归执行的数据集。也就是说每次递归使用的数据集都是上次递归的结果直到没有新的数据产生后递归结束将每一次递归的数据进行聚合就拿到了最终的数据集
UNION 和 UNION ALL
UNION: 会将本次递归查询到的数据进行内部去重也会和之前递归查询出的数据进行去重UNION ALL: 不会对数据进行去重
举个例子
// 创建表
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
);// 插入示例数据有两条数据是一样的
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, 浦东新区, 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, 徐汇区, 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, 徐汇区, 2020-03-28 15:56:39.664924, 2020-03-28 15:56:39.664924, 2);
使用 UNION ALL 进行数据查询
with recursive sub_shanghai as (select id, name, parent_idfrom document_directorieswhere id2union allselect dd.id, dd.name, dd.parent_idfrom document_directories ddjoin sub_shanghai on dd.parent_idsub_shanghai.id)select * from sub_shanghai;
结果如下 使用 UNION 进行查询
with recursive sub_shanghai as (select id, name, parent_idfrom document_directorieswhere id2unionselect dd.id, dd.name, dd.parent_idfrom document_directories ddjoin sub_shanghai on dd.parent_idsub_shanghai.id)select * from sub_shanghai;
得到结果如下 我们修改下原始数据再看下去重逻辑的区别
update document_directories set parent_id 2 where id2;
当我们使用 UNION 进行递归查询时结果并没有发生变化。但是当我们使用 UNION ALL 进行查询时会一直执行。这是因为 UNION ALL 不会将数据进行去重而每次递归查询的时候总归能查询到 {id: 5, name:上海, parent_id: 2} 这条数据所以递归就没有终止条件。
从而也验证了UNION 不但会将本次递归查询的数据进行内部去重也会和之前的递归结果进行去重。
