树形结构是一类重要的非线性结构,在关系型数据库中如何对具有树形结构的表进行查询,从而得到所需的数据是一个常见的问题。
关系型数据库
将数据按表结构形式进行组织。它对表格的处理方便灵活,且易学易用,因而得到广泛的应用。关系型数据库所处理的表格是线性结构的,表的每一行对应着一个数据元素,称做一条记录。记录与记录之间呈线性排列,彼此间没有联系。
然而,在解决实际问题时,常常会遇到非线性结构的数据。如下表所示,每一条纪录中的上级代码,就和其他纪录有着联系,这样就形成了一棵具有层次结构的树,它可以用下面的图来形象地表示:
树形结构
是一种结点之间有分支,并具有层次关系的结构,它非常类似于自
然界中的树。
树结构在客观世界中大量存在,例如家谱、行政组织机构都可用树形象地表示。树在计算机领域中也有着广泛的应用,例如在编译程序中,用树来表示源程序的语法
结构;在数据库系统中,用树来组织信息;在分析算法的行为时,用树来描述其执行过程。
在关系数据库中对具有树型结构的表常用的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 [标记]
--结果:
分享到:
相关推荐
mysql 树形结构查询,使用存储过程,实现mysql的树形结构查询
oracle树状结构查询即层次递归查询,是sql语句经常用到的,在实际开发中组织结构实现及其层次化实现功能也是经常遇到的。这篇文章给大家介绍了Oracle递归树形结构查询功能,需要的朋友参考下
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
针对父子级数据目录查询, 以前都是逐级的去根据父级id查询子集目录, 查出后最后再在代码中拼成树形结构, 相当复杂,我们可以利用 mybatis 提供的 collection 标签自动组织树形结构
软件开发经常要用到树形结构,采用sql实现树形结构是一种不错的方式,此例支持sql2005
最新全国省市区行政划分表,树形结构,全国统一(城市编码、区域编码、邮政编码),及各市区行政坐标(经纬度),方便初始化地图中心及标注点,等级划分三级联动效果
国家统计局-2019最新-省市县乡镇村-数据树形结构sql,5级到村,绝不骗人,亲测有效,实用方便.
只需要修改下面指定修改部分即可使用,原则上支持树形展开和排序,但由于实际数据量很大,因此可以按料段展开(U9 V3.0),欢迎交流。
树形结构(增删改查刷新等功能附SQL脚本) 是java编写 。jsp + serlvet 。实现 一个可视化。增删改查的树形结构。
一种基于树形结构的Sql结果集向Json数据的转换算法.pdf
sql 查询 所有子节点 方法已经调试通过(sql-server 2005)
部分普通sql查询在hive中的实现方式详细说明;
下面小编就为大家带来一篇sqlserver实现树形结构递归查询(无限极分类)的方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
vs2008+sqlserver2005可实现无限级分类的树形结构、内有数据库直接附加就可以用
开发工具Vs2010+ Mvc3+ SQL2005+ 自动生成数据库 N级树形结构
当使用树形控件时,数据库中的表怎么设计,这里可以得到参考
Delphi中快速实现数据库树形结构并实现Treeview导航表数据,仿论文实现的代码,可以操作数据库的单编号。
--树形sql查询 WITH TREE AS( --创建一个虚拟表 SELECT * FROM sys_organiz --指定需要查询的表 WHERE organize_code = '100000' -- 指定父节点条件 UNION ALL --联合查询 SELECT sys_organiz.* FROM sys_...
JAVA_SQL递归树形,用递归算法结合数据库对J2EE实现树结构