`

PostgreSQL下迭代实现数据库递归查询

阅读更多

前言:

考虑这样一个问题,A机构的上级机构是B,B机构的上级机构是C,C机构的上级机构是D,而D机构的上级机构是E。现在需要找出一个给定机构(例如E例如A)的所有上级机构。

这种问题的典型表结构如下

机构(ins_id) 上级机构(pre_ins_id)
A B
B C
C D
D E

 

今天我们就是要解决诸如此类的问题。

下面的函数以in_ins_id为参数,计算并返回该机构的所有直接或者间接上级机构并返回他们组成的集合

 

CREATE FUNCTION getAllPreIns(VARCHAR)	--接收一个VARCHAR参数
 RETURNS TABLE(ins_id VARCHAR)	--返回一个table
 LANGUAGE plpgsql
AS $function$	--function标识
DECLARE in_ins_id ALIAS FOR $1;    --把接收的的参数放在in_ins_id变量中

BEGIN
	CREATE TEMPORARY TABLE ins(ins_id VARCHAR(8));	--该临时表将返回最终的数据	
	CREATE TEMPORARY TABLE new_ins(ins_id VARCHAR(8)); --存储前一次迭代中找到的数据
	CREATE TEMPORARY TABLE temp(ins_id VARCHAR(8));	--用于存放对集合操作时的中间数据
	--先把in_ins_id的上级机构插入new_ins表中
	INSERT INTO new_ins
		SELECT pre_ins_id
		FROM ins_table
		WHERE ins_table.ins_id = in_ins_id;
	LOOP
		--将new_ins中的数据插入到ins中
		INSERT INTO ins
			SELECT new_ins.ins_id
			FROM new_ins;
		--将new_ins中ins_id的上级机构插入到temp中
		INSERT INTO temp
			(SELECT ins_table.pre_ins_id
				FROM new_ins, ins_table
				WHERE new_ins.ins_id = ins_table.ins_id)
			EXCEPT	--防止出现机构环如A的上级机构是B,B的上级机构是C,C的上级机构是A
			(SELECT ins.ins_id
				FROM ins);
		DELETE FROM new_ins;	--清空new_ins
		INSERT INTO new_ins		--保存temp
			SELECT temp.ins_id
			FROM temp;
		DELETE FROM temp;
		EXIT WHEN NOT EXISTS (SELECT new_ins.ins_id FROM new_ins);	--知道没有上级机构市循环终止
	END LOOP;
	RETURN QUERY SELECT ins.ins_id FROM ins;	--返回
END;

$function$	--function标识

 

 

假设我们传入的参数是A,在此function中,我们先找到A的上级机构B并存放在new_ins中,如此循环,直到某次循环中没有新的机构加进来才停止。

 

此过程所用到的4个表

  • ins:存储要返回的元素集合
  • new_ins:存储每一次迭代中找到的上级机构,并在下一次迭代中插入到表ins中
  • temp:临时存储每一次迭代中找到的上级机构
  • Ins_table:我们的原始机构表(如下图)

 

SELECT ins_id, pre_ins_id FROM ins_table; 

 


 

在LOOP循环之前我们先把in_ins_id的所有直接上级机构插入到表new_ins中。LOOP循环开始首先将new_ins中的所有机构插入到ins中。然后为new_ins中的所有机构计算上级机构,并去掉此前已经计算出的in_ins_id的上级机构,把余下的机构插入到temp中。最后用temp的数据替换new_ins。当new_ins为空时,LOOP终止。

 

最后我们在postgresql中调用该函数

 

SELECT * FROM getAllPreIns('A');

 结果如下图

 


 

结束语:以上的SQL语句全部是在PostgreSQL中运行通过,由于各种数据库的语法的差别,在其他数据库中并不能保证不会报错。由于本人水平所限,如有错误,欢迎批评指正。

 

参考文献:

1. 《数据库系统概念》第六版第五章:高级SQL

2. http://postgres.cz/wiki/PL/pgSQL_(en)

 

 

版权声明:本文为博主原创文章,未经博主允许不得转载。

  • 大小: 8.2 KB
  • 大小: 2.8 KB
1
0
分享到:
评论

相关推荐

    一种MySQL数据库SQL递归查询的研究与实现.pdf

    【MySQL数据库SQL递归查询的研究与实现】 MySQL是一种广泛应用的关系型数据库管理系统,以其开源、高效和跨平台等特性,尤其适合中小型网站的部署。虽然MySQL在处理常规数据操作和查询方面表现出色,但在某些特定...

    使用递归循环读取省市区json文件数据,并保存到数据库中

    - 递归深度可能会受到限制,对于大型数据,可能需要考虑非递归的迭代方法。 - 数据库操作需考虑事务处理,确保数据的一致性和完整性。 6. **应用实例**: 这种方法常用于系统初始化,如新部署的Web应用需要填充...

    PostgreSQL_与_MS_SQLServer比较

    而在 **PostgreSQL** 中,除了支持这些结构外,还提供了 `LOOP` 和 `FOR` 循环结构,后者用于迭代集合或数组。 #### 存储过程和函数 - **创建存储过程或函数** 的语法在两者之间也有所不同。在 **MS SQL Server** ...

    PostgreSQL在核心交易系统的应用.pptx

    其支持窗口函数、递归查询、物化视图等功能,满足复杂查询需求,为交易处理提供强大支持。 ### 4. 扩展性与灵活性 PostgreSQL 的插件架构允许用户根据业务需求定制功能,如哈希索引、GiST索引、SP-GiST索引等,...

    递归函数,称其为self

    在PostgreSQL中,我们可以使用`WITH RECURSIVE`语句来实现递归查询。这个语法允许我们创建一个临时的、可迭代的视图,每次迭代都会调用自身以获取下一层的数据。例如,假设我们有一个`employees`表,其中包含`...

    用数据库实现树形结构

    递归查询利用数据库的内置支持来迭代地遍历层次,而自连接则是通过将表自身与自身关联来模拟层级关系。 1. **递归查询**: - 在SQL Server中,可以使用`WITH RECURSIVE`语句来实现递归查询。这种方法允许定义一个...

    [源码][主机域名]易捷域名查询系统v1.0_ej99domainv1.0.rar

    系统会将用户输入的域名转换为相应的IP地址,这需要通过递归或迭代的DNS查询来完成。DNS服务器层次结构中的根服务器、顶级域服务器、权威域名服务器和缓存域名服务器共同协作完成这一过程。 4. 实时查询与缓存机制...

    Tanzu Greenplum数据平台产品介绍.pdf

    在数据库功能方面,Tanzu Greenplum遵循ANSI SQL标准,兼容PostgreSQL 9.x的所有功能,如JSON、XML支持、递归查询、Raster PostGIS等。它还支持范围和列表分区,以及多层次的数据分区组合。同时,Greenplum提供了...

    PHP5操作手册

    3. PDO:PHP5中的PDO提供了统一的数据库访问接口,支持多种数据库系统,如MySQL、SQLite、PostgreSQL等。这使得代码更具有可移植性,并且支持预处理语句,提高了安全性。 4. SPL(Standard PHP Library):PHP5内置...

    PHP4.3树形论坛

    在树形论坛中,这需要实现递归或迭代的逻辑,正确地构建出层级结构。 6. **用户交互**:包括表单提交、按钮点击事件处理、分页功能、搜索功能等,这些都需要用到JavaScript和HTML的交互。 7. **安全性**:考虑到SQL...

    鸟哥Linux私房菜

    - **DNS解析**:了解DNS工作流程,包括递归查询与迭代查询的区别。 #### 三、服务器搭建与维护 **3.1 服务器安装与配置** - **CentOS/RHEL/Fedora系统**:基于这些发行版进行服务器的安装与配置,因为它们提供了...

    最新PHP5中文手册 (CHM格式)

    4. **PDO(PHP Data Objects)**:提供了一个数据访问接口,支持多种数据库系统,如MySQL、SQLite、PostgreSQL等,提供了统一的数据库操作方式。 5. **SimpleXML**:PHP5内建了对XML数据的处理支持,使得解析和操作...

    PHP5手册-最新版 2012-5-31

    5. PDO(PHP Data Objects):提供了一个数据访问抽象层,支持多种数据库系统,如MySQL、SQLite、PostgreSQL等,提高了代码的可移植性。 6. SimpleXML:一个内置的库,用于解析和操作XML文档,简化了XML处理。 7. ...

    PythonChallenge_Source:pythonchalleng.com的源文件-python source file

    Python支持多种数据库接口,如SQLite、MySQL和PostgreSQL,通过Python的DB-API或者ORM(对象关系映射)如SQLAlchemy来操作。PythonChallenge可能保存用户的进度、得分等信息,因此会涉及到数据的增删改查操作。 ...

    PPHP_introduction_and_application_of_loop.rar_WEB开发_PHP_

    PHP最初由Rasmus Lerdorf在1995年创建,后来发展成为一种功能强大的服务器端编程工具,支持多种数据库系统,如MySQL、PostgreSQL等,并且具有跨平台性,可以在Linux、Windows、Mac OS等多种操作系统上运行。...

Global site tag (gtag.js) - Google Analytics