树形结构是一类重要的非线性结构,在关系型数据库中如何对具有树形结构的表进行查询,从而得到所需的数据是一个常见的问题。
关系型数据库将数据按表结构形式进行组织。它对表格的处理方便灵活,且易学易用,因而得到广泛的应用。关系型数据库所处理的表格是线性结构的,表的每一行对应着一个数据元素,称做一条记录。记录与记录之间呈线性排列,彼此间没有联系。
然而,在解决实际问题时,常常会遇到非线性结构的数据。如下表所示,每一条纪录中的上级代码,就和其他纪录有着联系,这样就形成了一棵具有层次结构的树,它可以用下面的图来形象地表示:
图1
树形结构是一种结点之间有分支,并具有层次关系的结构,它非常类似于自然界中的树。 树结构在客观世界中大量存在,例如家谱、行政组织机构都可用树形象地表示。树在计算机领域中也有着广泛的应用,例如在编译程序中,用树来表示源程序的语法结构;在数据库系统中,用树来组织信息;在分析算法的行为时,用树来描述其执行过程。
在关系数据库中对具有树型结构的表常用的3种查询:
1. 节点A的位于第n层的父亲节点的信息。如:员工黄菁菁的两级上司的信息。
--创建函数GetManager
CREATE FUNCTION dbo.GetManager ( @employee_id AS char(5), @level AS int = 1)
RETURNS char(5) AS
BEGIN
IF @level = 0
RETURN @employee_id
--如果 @level 为0,表示已经找到其上司号码
RETURN dbo.GetManager(
(SELECT [上级号码] FROM [员工信息] WHERE [员工号码] = @employee_id), @level -1)
-- 如果 @level 大于 0,则返回直接上司的 @level-1 级的上司号码
END
--员工黄菁菁的两级上司的信息
SELECT * FROM [员工信息] WHERE [员工号码] =dbo.GetManager('E9907',2 )
2.某棵子树的统计信息,如:员工余顺景及其所有下属员工的工资总额。
同样使用递归的算法:
如果没有下属,则返回当前的工资额; 如果有下属,则返回所有下属的工资总额。
CREATE FUNCTION dbo.GetTotalSalary ( @manager_id AS char(5) ) RETURNS int AS
BEGIN
RETURN(
SELECT [工资] FROM [员工信息] WHERE [员工号码] = @manager_id) +
CASE WHEN EXISTS(SELECT * FROM [员工信息] WHERE [上级号码] = @manager_id)
THEN ( SELECT SUM(dbo.GetTotalSalary([员工号码])) FROM [员工信息]
WHERE [上级号码] = @manager_id
)
ELSE 0
END
END
--员工逍遥及其下属的工资总额
SELECT dbo.GetTotalSalary('E9906') AS 工资总额
3. 某棵子树的结点信息,如:员工郑可可及其所有下属员工的信息
a.求树的深度:
CREATE FUNCTION dbo.GetUnderlyingLevel ( @manager_id AS char(5) )
RETURNS int AS
BEGIN
RETURN
CASE
WHEN EXISTS(SELECT * FROM [员工信息] WHERE [上级号码] = @manager_id)
THEN 1 + (SELECT MAX(dbo.GetUnderlyingLevel([员工号码])) FROM [员工信息] WHERE [上级号码] = @manager_id)
ELSE 1
END
END
如:SELECT dbo.GetUnderlyingLevel('E9901') AS '下属级别'
---返回:4
b 求某一个节点所有的子节点的信息。
由于返回的是一个结果集,所以需要用table数据类型来存储
CREATE FUNCTION dbo.GetSubtreeInfo ( @manager_id AS char(5) )
RETURNS @treeinfo table
([员工号码] [char] (5) NOT NULL,
[姓名] [char] (10) NOT NULL,
[年龄] [int] NOT NULL,
[工资] [money] NOT NULL,
[上级号码] [char] (5) NULL,
[级别] [int] NOT NULL
) AS
BEGIN
DECLARE @level AS int
SELECT @level = 0
INSERT INTO @treeinfo
SELECT [员工号码], [姓名], [年龄], [工资], [上级号码], @level
FROM [员工信息]
WHERE [员工号码] = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT E.[员工号码], E.[姓名], E.[年龄], E.[工资], E.[上级号码], @level
FROM [员工信息] AS E JOIN @treeinfo AS T
ON E.[上级号码] = T.[员工号码] AND T.[级别] = @level - 1
END
RETURN
END
--如:查询郑可可及其下属的信息
SELECT * FROM dbo.GetSubtreeInfo('E9903')
--结果:
c .对b进行改进,将该树型结构以图形化的方式打印出来。
由于打印要进行排序,所以加了一个标记字段。
CREATE FUNCTION dbo.GetSubtreeInfo2 ( @manager_id AS char(5) )
RETURNS @treeinfo table
([员工号码] [char] (5) NOT NULL,
[姓名] [char] (10) NOT NULL,
[年龄] [int] NOT NULL,
[工资] [money] NOT NULL,
[上级号码] [char] (5) NULL,
[级别] [int] NOT NULL,
[标记] [varchar] (200) NOT NULL
) AS
BEGIN
DECLARE @level AS int, @path AS varchar(200)
SELECT @level = 0, @path = 'NULL'
INSERT INTO @treeinfo
SELECT [员工号码], [姓名], [年龄], [工资], [上级号码], @level, 'NULL->'+ [员工号码]
FROM [员工信息]
WHERE [员工号码] = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT E.[员工号码], E.[姓名], E.[年龄], E.[工资], E.[上级号码], @level, T.[标记] + '->'+ E.[员工号码]
FROM [员工信息] AS E JOIN @treeinfo AS T
ON E.[上级号码] = T.[员工号码] AND T.[级别] = @level - 1
END
RETURN
END
--如:
SELECT REPLICATE ('| ', [级别]) + [姓名] AS 组织结构 FROM dbo.GetSubtreeInfo2('E9901') order by [标记]
--结果:
图2
- 大小: 24.3 KB
- 大小: 6.9 KB
分享到:
相关推荐
mysql 树形结构查询 MySQL 树形结构查询是指使用存储过程来实现 MySQL 数据库中的树形结构查询。这种查询方式可以高效地查询树形结构的数据,并且可以根据需要设置递归深度。 MySQL 中的树形结构查询可以使用存储...
Oracle数据库在处理树形结构数据时提供了强大的递归查询功能,这种特性对于组织结构、产品分类、层级菜单等场景的应用非常广泛。递归树形结构查询主要依赖于`CONNECT BY`和`PRIOR`关键字,它们允许我们构建复杂的...
"一种基于树形结构的Sql结果集向Json数据的转换算法" 本文提出了一种基于树形结构的Sql结果集向Json数据的转换算法,旨在解决关系型数据库中树结构数据与Web应用中Json数据格式不一致的问题。该算法将Sql结果集转换...
SqlServer树形结构、层次查询 结果如下: 1 1 2 1-2 4 1-2-4 5 1-2-5 10 1-2-5-10 8 1-2-5-8 6 1-2-6 3 1-3 7 1-3-7 11 1-3-7-11 9 1-3-7-9 12 1-3-7-9-12 13 1-3-7-9-13 14 1-3-7-9-14
在许多应用场景中,我们可能需要将扁平化的数据结构转换为树形结构,以便更好地理解和操作数据。扁平化数据通常是线性的,而树形结构则能反映数据之间的层级关系,这对于表示目录、菜单、组织结构等非常有用。本话题...
本文将深入探讨如何使用MyBatis进行父子级树形结构查询,避免繁琐的代码拼接。 首先,我们了解MyBatis的`collection`标签。在MyBatis的映射XML文件中,`<collection>`标签用于表示一对多的关系,它允许我们在一次...
尤其是在处理具有层级关系的数据时,例如组织架构、产品分类等场景,利用SQL实现树形结构可以极大地提高查询效率与数据管理的便捷性。本文将通过一个具体的示例来介绍如何在SQL Server 2005环境中使用`WITH`语句来...
在本示例中,我们将探讨如何使用Spring JPA来实现单表递归树形结构。 首先,我们需要理解递归树形结构。在数据库中,树形结构通常通过自关联来表示,即一个表的某个字段引用该表自身,形成一个层级关系。对于单表...
- **查询**:通过遍历树形结构,查找满足特定条件的节点。 5. **可视化展示**: - 项目使用JSP来实现前端的可视化展示,可能借助于JavaScript库如jQuery或更现代的React、Vue等,将后端返回的树形数据转化为用户...
最新全国省市区行政划分表,树形结构,全国统一(城市编码、区域编码、邮政编码),及各市区行政坐标(经纬度),方便初始化地图中心及标注点,等级划分三级联动效果
国家统计局-2019最新-省市县乡镇村-数据树形结构sql,5级到村,绝不骗人,亲测有效,实用方便.
本文将深入探讨如何在MySQL中查询树形结构的全部子项列表,结合提供的`MySql_Link_Function.sql`文件,我们将探讨一种有效的方法来实现这一功能。 首先,树形结构在数据库中的存储通常采用自引用的方式,即每个节点...
本项目“BS实现树形结构(jsp+mysql数据库+设计文档)”提供了一个完整的解决方案,包括源码、开发文档以及SQL Server数据库,方便开发者在MyEclipse环境中直接导入使用。 首先,我们来探讨树形结构在Web开发中的应用...
只需要修改下面指定修改部分即可使用,原则上支持树形展开和排序,但由于实际数据量很大,因此可以按料段展开(U9 V3.0),欢迎交流。
Oracle数据库提供了强大的查询功能,使得对这类树形结构数据的查询变得简单而高效。本文将深入探讨如何使用Oracle的特定查询语法来检索树形结构数据。 首先,要了解的是,在Oracle中,树形结构数据的查询主要依赖于...
sql 查询 所有子节点 方法已经调试通过(sql-server 2005)
在SQL Server中,实现树形结构递归查询是数据库管理中的常见需求,特别是在处理具有层级关系的数据时,如无限级分类。SQL Server 2005及以后版本引入了公用表表达式(CTE,Common Table Expression)来支持递归查询...
### 部分普通SQL查询在Hive中的实现方式 Hive是一款基于Hadoop的数据仓库工具,能够对存储在Hadoop文件系统中的数据集进行数据提取、转换、加载(ETL),这是一种可以简化MapReduce编程的工具。由于Hive的设计初衷...
通过`树形结构.sql`文件,你可以进一步研究和实践这些示例,加深对MSSQL查询树形结构的理解。 总结,理解和掌握在MSSQL中查询树形结构的方法,对于开发和维护涉及层级关系的数据库应用至关重要。无论是正查还是反查...