在SQL Server中巧妙存储树形结构数据

合集下载

SQLServer树形数据结构的数据进行数据统计

SQLServer树形数据结构的数据进⾏数据统计前⾔前⼏天朋友问我，关于SQLServer数据库中对树形结构的表数据统计问题，需求⼤致如下：分类表（递归数据），A的⼦分类是B，B的⼦分类是C……分类关系不间断，A为第⼀层，B为第⼆层，C为第三层……需要统计“每个分类所在的层数”、“⼦分类的总数”和“⼦分类的层数”。

解决思路：创建⽰例表结构，代码如下：-- 分类⽰例表create table temp_class(classId int primary key identity(1,1), -- 分类ID，主键，递增className nvarchar(50), -- 分类名称pcId int default0, -- ⽗级分类ID，0表⽰最顶层uLevel int, -- 层数nextUCount int, -- ⼦分类的总数nextLevelCount int-- ⼦分类的层数);-- 层数字段添加索引-- create index ind_tempClass_uLevel on temp_class(uLevel);-- 添加测试数据。

步骤⼀：每个分类所在的层数根据树形数据结构的规律，在统计层数时，需要从最顶层向下累计，代码如下：-- 1、更新层数（pcId=0 表⽰第⼀层）-- 更新最顶层declare@i int=1; -- 第⼀层update temp_class set uLevel=@i where pcId=0;while(1=1)beginif(not exists(select top11from temp_class a where exists(select top11from temp_class b where b.uLevel=@i and b.classId=a.pcId)))break; -- ⽆下层数据，跳出循环-- 更新下⼀层update a set a.uLevel=@i+1from temp_class a where exists(select top11from temp_class b where b.uLevel=@i and b.classId=a.pcId);-- 增加⼀层set@i=@i+1;end;步骤⼆：⼦分类的总数在第⼀步中，已经统计出了分类的层数，在统计每个分类的⼦分类个数时，就从最底层统计起来，本层⼦分类的个数就等于下⼀层中⼦分类的个数之和加上下⼀层的分类数量，代码如下：-- 2、更新⼦分类的总数-- 获取最低层分类（最⼤的层数）declare@maxLevel int=1;select@maxLevel=MAX(uLevel) from temp_class;-- 更新最底层的⼦分类总数为 0update temp_class set nextUCount=0where uLevel=@maxLevel;-- 从最底层向上累计⼦分类总数while(1=1)beginset@maxLevel=@maxLevel-1;if(@maxLevel<=0) -- 层数⾛完，退出break;-- 更新上⼀层的⼦分类的总数update a set a.nextUCount=isnull(b.nextUCount,0) from temp_class aleft join-- ⽗级（本层）分类的个数=下⼀层中⼦分类的个数之和+下⼀层的分类数量(select pcId,SUM(nextUCount)+COUNT(classId) nextUCount from temp_class where uLevel=@maxLevel+1group by pcId) bon a.classId=b.pcIdwhere a.uLevel=@maxLevel;end;步骤三：⼦分类的层数在第⼀步中，已经统计出了分类的层数，在统计每个分类的⼦分类层数时，就从最底层统计起来，本层⼦分类的层数就等于下⼀层中⼦分类的层数最⼤值加上⼀层，代码如下：-- 3、更新⼦分类的层数-- 获取最低层⼦分类（最⼤的层数）declare@maxLevel int=1;select@maxLevel=MAX(uLevel) from temp_class;-- 更新最底层的⼦分类层数为 0update temp_class set nextLevelCount=0where uLevel=@maxLevel;-- 从最底层向上累计层数while(1=1)beginset@maxLevel=@maxLevel-1;if(@maxLevel<=0) -- 层数⾛完，退出break;-- 更新上⼀层的⼦分类层数update a set a.nextLevelCount=ISNULL(b.nextLevelCount,0) from temp_class aleft join-- ⽗级（本层）分类的层数=下⼀层中⼦分类的最⼤层数+1（当前⼦分类为 1 层）(select pcId,(MAX(nextLevelCount)+1) as nextLevelCount from temp_class where uLevel=@maxLevel+1group by pcId) b on a.classId=b.pcIdwhere a.uLevel=@maxLevel;end;查询结果：后⾔该随笔仅当个⼈笔记所⽤，路过的⼤神如有好的建议，还请赐教，菜鸟再此感激不尽！。

关系型数据库存储树形结构

关系型数据库存储树形结构树形结构是一种常见的数据结构，它由一个根节点和若干个子节点组成，每个节点可以有多个子节点，但只能有一个父节点。

在实际应用中，树形结构经常用于表达层级关系，如组织结构、分类体系等。

在关系型数据库中，如何存储和查询树形结构是一个常见的挑战。

传统的关系型数据库通常采用表格的形式来存储数据，每个表格由若干列和若干行组成。

然而，表格的结构并不适合直接存储树形结构。

为了解决这个问题，有几种常见的方法可以在关系型数据库中存储树形结构。

1. 嵌套集合模型（Nested Set Model）嵌套集合模型是一种常见的存储树形结构的方法。

它通过为每个节点分配一个左右值来表示节点在树中的位置。

通过使用左右值，可以快速查询某个节点的子节点、父节点以及兄弟节点。

然而，嵌套集合模型在插入、删除节点时需要更新大量的左右值，因此对于频繁变动的树形结构来说，性能可能不佳。

2. 路径枚举模型（Path Enumeration Model）路径枚举模型是另一种存储树形结构的方式。

它通过为每个节点存储一个路径来表示节点在树中的位置。

路径是一个由父节点的标识符组成的字符串，可以使用特定的分隔符来分隔各个节点。

通过路径，可以快速查询某个节点的子节点、父节点以及兄弟节点。

路径枚举模型在插入、删除节点时的性能较好，但在查询深层次节点时可能存在性能问题。

3. 闭包表模型（Closure Table Model）闭包表模型是一种更为灵活的存储树形结构的方法。

它通过使用一个闭包表来存储节点之间的关系。

闭包表是一个由祖先节点和后代节点组成的表，通过递归查询闭包表，可以获取任意节点的所有祖先节点和后代节点。

闭包表模型的优势在于查询灵活，但在插入、删除节点时需要更新闭包表，因此性能可能较差。

除了以上三种常见的存储树形结构的方法，还有其他一些变种模型，如材料化路径模型（Materialized Path Model）、嵌套集合模型的改进版（Modified Preorder Tree Traversal Model）等。

组织结构 sql 按树形创建

组织结构 sql 按树形创建在许多组织中，组织结构往往呈现出树形的层级关系，例如公司的部门、子部门和员工之间的关系。

使用 SQL 语言可以方便地创建和维护这种树形结构，从而实现组织结构的灵活管理。

我们需要定义一个用于存储组织结构的表。

该表包含以下字段：ID、名称、父级ID。

其中，ID 是每个节点的唯一标识符，名称是节点的名称，父级ID 是指向父级节点的标识符。

通过这种方式，可以将各个节点组织成一个树形结构。

接下来，我们可以使用SQL 语句来创建组织结构的表。

具体的SQL 语句如下所示：```sqlCREATE TABLE organization (ID INT PRIMARY KEY,name VARCHAR(255),parent_id INT);```通过上述SQL 语句，我们成功创建了一个名为organization 的表，其中包含了 ID、名称和父级ID 这三个字段。

接下来，我们可以向该表中插入数据，构建具体的组织结构。

例如，我们可以插入一些部门和员工的数据，如下所示：```sqlINSERT INTO organization (ID, name, parent_id) VALUES (1, '公司', NULL);INSERT INTO organization (ID, name, parent_id) VALUES (2, '人事部', 1);INSERT INTO organization (ID, name, parent_id) VALUES (3, '财务部', 1);INSERT INTO organization (ID, name, parent_id) VALUES (4, '技术部', 1);INSERT INTO organization (ID, name, parent_id) VALUES (5, '人事部子部门1', 2);INSERT INTO organization (ID, name, parent_id) VALUES (6, '人事部子部门2', 2);INSERT INTO organization (ID, name, parent_id) VALUES (7, '技术部子部门1', 4);INSERT INTO organization (ID, name, parent_id) VALUES (8, '技术部子部门2', 4);INSERT INTO organization (ID, name, parent_id) VALUES (9, '财务部子部门1', 3);```通过上述SQL 语句，我们成功向organization 表中插入了一些测试数据，构建了一个简单的组织结构。

sqlserver索引的结构及其存储,索引内容

sqlserver索引的结构及其存储,索引内容sqlserver 索引的结构及其存储,sql server索引内容⽂章转载，原⽂地址:本⽂关注以下⽅⾯(本⽂所有的讨论基于SQL Server数据库)：索引的分类；索引的结构；索引的存储⼀、索引定义分类让我们先来回答⼏个问题：什么是索引？索引是对数据库表中⼀列或多列的值进⾏排序的⼀种结构，使⽤索引可快速访问数据库表中的特定信息。

举个例⼦，索引就像我们查字典时⽤的按拼⾳或笔画或偏旁部⾸有哪些索引？从物理结构上可分为两种：聚集索引和⾮聚集索引(此外还有空间索引、筛选索引、XML索引)索引说明( )每张表上最⼤的聚集索引数为1；每张表上最⼤的⾮聚集索引数为999；每个索引最多能包含的键列数为16；索引键记录⼤⼩最多为900字节⼆、索引数据结构在SQL Server数据库中，索引的存储是以B+树（注意和⼆叉树的区别）结构来存储的，⼜称索引树，其节点类型为如下两种：索引节点；叶⼦节点索引节点按照层级关系，有时⼜可以分为根节点和中间节点，其本质是⼀样的，都只包含下⼀层节点的⼊⼝值和⼊⼝指针；叶⼦节点就不同了，它包含数据，这个数据可能是表中真实的数据⾏，也有可能是索引列值和⾏书签，前者对应于聚集索引，后者对应于⾮聚集索引。

三、索引存储结构在正式讨论索引的存储结构之前，我们有必要先来了解⼀下SQL Server数据库的存储结构。

SQL Server数据库存储（结构）的最⼩单位是页，⼤⼩为8K，共8 * 1024 = 8192Byte，不论是数据页还是索引页都是以此⽅式存放。

实际上对于SQL Server数据库⽽⾔，其页（Page）类型有很多种，⼤概有如下⼗⼏种（）：Type 1 – Data page.Data records in heapClustered index leaf-levelLocation can be randomType 2 – Index pageNon-clustered indexNon-leave-level clustered indexLocation can be randomType 3 – Text Mixed PageSmall LOB value(s), multiple types and rows.Location can be randomType 4 – Text PageLOB value from a single column valueLocation can be randomType 7 – Sort PageTemporary page for sort operation.Usually tempdb, but can be in user database for online operations.Location can be randomType 8 – GAM PageGlobal Allocation Map, track allocation of extents.One bit for each extent, if the bit is 1, means the extent is free, otherwise means the extent is allocated (not necessary full).The first GAM page in each file is page 2Type 9 – SGAM PageShared Global Allocation Map, track allocation of shared extentsOne bit for each extent, if the bit is 1, means the extent is allocated but has free space, otherwise means the extent is fullThe first SGAM page in each file is page 3Type 10 – IAM PageIndex Allocation Map. Extent allocation in a GAM interval for an index or heap table.Location can be random.Type 11 – PFS PagePage Free Space. Byte map, keeps track of free space of pagesThe first PFS is page 1 in each file.Type 13 – Boot PageInformation about the pageOnly page 9 in file 1.Type 14 – Server Configuration Page (It may not be the official name)Part of information returned from sp_configure.It only exists in master database, file 1, page 10SQL Server 2008 OnlyType 15 – File Header PageInformation about the file.It's always page 0 every data page.Type 16 – Differential Changed mapExtents in GAM interval have changed since last full or differential backupThe first Differential Changed Page is page 6 in each fileType 17 – Bulk Change MapExtents in GAM interval modified by bulk operations since last backupThe first Bulk Change Map page is page 7 in each file 表中所有数据页的存放在磁盘上⼜有两种组织⽅式：堆表；索引组织表如果表中所有数据页是以⼀种页间⽆序、随机存储的⽅式，则称这样的表为堆表；否则如果表中数据页间按某种⽅式（如表中某个字段）有序地存储与磁盘上，则称为索引组织表。

SqlServer递归查询树

SqlServer递归查询树递归关于进⾏树形结构的查询：⼀：简单的树形结构代码。

-- with⼀个临时表(括号中是你要查询的列名)with temp(ID,PID,Name,curLevel)as(--1：初始查询（这⾥的PID=-1 在我的数据中是最底层的根节点）select ID,PID,Name,1 as level from dbo.T_ACL_OUwhere Deleted = 0 and PID = -1union all--2：递归条件select a.ID,a.PID,, b.curLevel+1from T_ACL_OU a --3：这⾥的临时表和原始数据表都必须使⽤别名，不然递归的时候不知道查询的是那个表的列inner jointemp bon ( a.PID=b.id) --这个关联关系很重要，⼀定要理解⼀下谁是谁的⽗节点)select * from temp --4：递归完成后⼀定不要少了这句查询语句否则会报错⼆：带缩进的树形机构with temp(ID,PID,Name,curLevel)as(--初始查询select ID,PID,Name,1 as curLevel from dbo.T_ACL_OUwhere Deleted = 0 and PID = -1union all--递归条件select a.ID,a.PID,convert(nvarchar(100),CONVERT(nvarchar(100), REPLICATE (' ', b.curLevel+1)+)) as Name , b.curLevel+1--这⾥的 REPLICATE函数⾮常重要，⽤于缩进空格⽤。

不懂得可以在SQLserver中选中后按F1键from T_ACL_OU ainner jointemp bon ( a.PID=b.id))select ID,PID,Name,curLevel from temp三:查询是否有⼦节点with temp(ID,PID,HandNo,Name,curLevel,pLevel,haveChild)as(--初始查询select ID,PID,HandNo,Name,1 as level,0 as pLevel,1 as haveChild from dbo.T_ACL_OUwhere Deleted = 0 and PID = -1union all--递归条件select a.ID,a.PID,a.HandNo,, b.curLevel+1,b.curLevel,haveChild= (case when exists(select 1 from T_ACL_OU where T_ACL_OU.PID=a.id) then 1 else 0 end)--(select 1 from T_ACL_OU where exists(select 1 from T_ACL_OU where a.PID=b.id))from T_ACL_OU ainner jointemp bon ( a.PID=b.id))select * from temp order by pLevel这3段代码可以直接复制使⽤，修改⼀下表名和要查询的列名基本上都是通⽤的.。

ssqlserver树状结构的sql第四级父子关系

SQL Server中的树状结构是指在数据库中使用父子关系来组织和管理数据的一种形式。

在这种结构中，每个数据项都与其他数据项之间存在着父子关系，通过这种关系可以很好地表示层次结构的数据。

一、树状结构的概念树状结构是一种非常常见的数据组织形式，它具有根节点、子节点和叶子节点等基本元素。

在SQL Server中，树状结构通常通过父子关系来表示，每个数据项都可能有一个或多个子节点，并且也可能有一个父节点。

这种结构在各种应用场景中都有着非常广泛的应用，如组织架构、产品分类、地区划分等。

二、树状结构的管理方法在SQL Server中，要管理树状结构的数据，通常会使用一些特定的方法和技术。

其中，最常用的方法包括递归查询、CTE(Common Table Expression)、层次查询等。

这些方法都可以很好地处理树状结构的数据，实现对树状结构的查询、插入、删除和更新等操作。

1. 递归查询递归查询是通过自身与自身的关联来实现对树状结构数据的查询。

在SQL Server中，可以使用递归的方式来查询指定节点的所有子节点，或者查询指定节点的所有父节点。

递归查询通常使用CTE来实现，通过递归的方式来逐层遍历整个树状结构，实现对数据的查询和操作。

2. CTECTE(Common Table Expression)是SQL Server中用来创建临时的、命名的查询结果集的一种技术。

通过CTE，可以很方便地实现对树状结构数据的递归查询和操作。

CTE通常结合递归查询来实现树状结构数据的处理，可以很好地提高查询的效率和可读性。

3. 层次查询层次查询是一种特殊的查询方式，适用于树状结构数据的查询和展示。

在SQL Server中，可以通过使用层次查询来获取树状结构数据的层次关系和结构。

通过层次查询，可以很方便地实现对树状结构数据的展示和分析。

三、树状结构的实际应用树状结构的数据在各种实际应用中都有着广泛的应用。

在SQL Server 中，树状结构的数据可以用来表示组织架构、产品分类、地区划分等信息。

sqlserver遍历表成一棵树结构

sqlserver遍历表成⼀棵树结构⼀棵树的层次结构都在⼀张表内，当有这样的需要的时候。

可以这样玩：<sql id="department_select_sql">with ldepartment as (selectdept_id,parent_id,0 as dept_level,row_number()over(order by getdate()) as orderidfromdepartmentwhere(parent_id is nullor parent_id = '')union allselecta.dept_id,a.parent_id,b.dept_level + 1 as dept_level,b.orderid*100+row_number()over(order by getdate()) as orderidfromdepartment a,ldepartment bwherea.parent_id =b.dept_id) selectt1.dept_level,t1.orderid,t2.paic_unique_deptid,t2.deptid_descr,t2.parent_id,(select aa.deptid_descr + '('+aa.dept_id+')' from department aa where aa.dept_id=t2.parent_id ) as parent_id_desc,t2.dept_id,t2.ou_type,t2.date_created,t2.created_by,t2.date_updated,t2.updated_by,t2.row_idfrom ldepartment t1, department t2where t1.dept_id = t2.dept_id<isNotEmpty prepend="and" property="deptid_descr">t2.deptid_descr like '%+#deptid_descr#+%'</isNotEmpty><isNotEmpty prepend="and" property="parent_id">t2.parent_id = #parent_id#</isNotEmpty>order by ltrim(t1.orderid) // 关键点（字符串排序）</sql>调⽤：<select id="department_find" parameterClass="java.util.Map"resultClass="com.pasc.supms.parameter.dto.DepartmentDTO"><include refid="department_select_sql"/></select>java对象：public class DepartmentDTO extends SupmsBaseDTO {private String paic_unique_deptid; // 部门唯⼀编号private String deptid_descr; // 部门名称private String parent_id; // 上级部门编号private String parent_id_desc; // 上级部门名称编号private String dept_id; // 部门属主编号private String ou_type; // 部门类型private String dept_level; // 部门层级jsp页⾯：<table cellpadding="0" cellspacing="0" class="table_list_2"><thead align="center"><tr><th >部门编号</th><th >部门名称</th><th >上级部门编号</th><th >部门类型</th></tr></thead><tbody align="center"><c:choose><c:when test="${not empty pageBean.resultList}"><c:forEach var="doc" items="${pageBean.resultList}" varStatus="i"><tr><td><c:forEach begin="1" end="${doc.dept_level }">-- </c:forEach>${doc.dept_id }</td><td>${doc.deptid_descr }</td><td>${doc.parent_id_desc }</td><td>${doc.ou_type }</td></tr></c:forEach></c:when><c:otherwise><tr id="noList"><td colspan="11" align="center">对不起，暂时还没有记录！</td></tr></c:otherwise></c:choose></tbody></table>结果：。

用SQL形成父子结点树结构的妙法

用SQL形成父子结点树结构的妙法湖南科创信息技术股份有限公司夏明伟摘要：在实际应用中经常会有些反映树型关系的数据，它们以父子结点的形式存储在库表中，为了能够直观地展示它们的带线树结构关系往往需要通过编程来实现。

本文通过对Oracle 的SQL语句的妙用，提出用一句Oracle的SQL语句实现带线树结构展示的妙想，此法在树型字典数据的选择中得到实际应用，简便实用大大提高了程序的可靠性，也为树型关系数据展示其带线树结构提供一种新颖的方法。

关键词：SQL；父子结点；树结构；展示A Clever Idea for the Use of SQL Forming the Father-child node Tree’s ConstructionXia MingweiThe Technology Center of Modern Education, Central South University, Changsha，ChinaAbstract: Data of tree-shaped construction is often found in its application. As a form of father-child node, it is stored in tables. Programming is needed to give a vivid presentation of the tree-shaped construction. This paper is intended for a clever idea, using SQL, based on its application. The method is applied in the choice of tree-shaped dictionary data. It’s simple and effective and could greatly improve the reliability of programs, offering an entirely new method to display tree-construction with lines for the data of tree-shaped construction.Key words: SQL；father-child node；tree construction；display一问题的提出在应用程序开发过程中经常会对字典表数据进行各种操作，特别对树型结构组织的字典表进行数据选择时人们总是希望以树型结构的方式一次展示出来供挑选，而不是以其他方式展示。

SQLServer树型结构数据处理的存储过程

SQLServer树型结构数据处理的存储过程我们在平常的系统开发中常常会遇到像⽆限级分类这样的树型结构数据，现提供⼀个可⽤的数据库存储过程，可以完成树型结构数据的排序。

环境：windows7+Sql Server 2008说明：下⾯代码已经转换成Sql server2000的脚本，处理效果如下，看sortname字段结果,代码经过测试。

创建树型表CREATE TABLE[dbo].[categories]([category_id][int]IDENTITY(1,1) NOT NULL,[parent_id][int]NULL,[category_name][varchar](500) NULL,CONSTRAINT[PK_categories]PRIMARY KEY CLUSTERED([category_id]ASC) ON[PRIMARY]) ON[PRIMARY]GO插⼊测试数据INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (1, 0, CONVERT(TEXT, N'A'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (2, 0, CONVERT(TEXT, N'B'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (3, 1, CONVERT(TEXT, N'AA'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (4, 3, CONVERT(TEXT, N'AAA'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (5, 2, CONVERT(TEXT, N'BB'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (6, 1, CONVERT(TEXT, N'AA2'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (7, 1, CONVERT(TEXT, N'AA3'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (8, 3, CONVERT(TEXT, N'AAA2'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (9, 4, CONVERT(TEXT, N'AAA3'))INSERT[dbo].[categories] ([category_id], [parent_id], [category_name]) VALUES (10, 2, CONVERT(TEXT, N'BB2'))创建存储过程，表中的level为级别深度，通过⽣成的临时表与分类表关联显⽰结果。

SQLServer处理树结构数据的一个示例

SQLServer处理树结构数据的⼀个⽰例没多少技术含量，在简单的系统⾥应⽤问题不⼤；解决的问题是：1.树结构数据的表设计；2.对任意⼀个节点，找到他所有的上级节点3.对任意⼀个节点，找到他所有的下级节点这⾥的部分SQL是同事给的，具体出处不详；废话不多说，直接看例⼦吧；1. 表设计，以⽐较常见的组织机构表为例，典型的树结构create table Orgnization (ID int not null,Name nvarchar(50) not null,Description nvarchar(300) null,Leader nvarchar(50) null,Status int null,Parent int not null,Path varchar(100) not null,constraint PK_ORGNIZATION primary key (ID))其中，Parent表⽰上级机构的ID，没有上级机构的话，就填0；需要关注的是Path字段，由所有上级节点ID拼成的字符串，以逗号分隔，如： 0,1,4 ， 4是⽗，1是爷，0是祖宗，依次类推2.为⽅便测试，插⼊⼀些数据INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(1, 'XXX集团', null, null, null, 0, '0');INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(2, '111事业部', null, null, null, 1, '0,1'); INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(3, '222事业部', null, null, null, 1, '0,1'); INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(4, '333事业部', null, null, null, 1, '0,1'); INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(5, 'xxx部', null, null, null, 2, '0,1,2');INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(6, 'xxx2部', null, null, null, 2, '0,1,2');INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(7, 'XXX3部', null, null, null, 2, '0,1,2'); INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(8, 'yyy部', null, null, null, 3, '0,1,3');INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(9, 'zzz部', null, null, null, 4, '0,1,4');INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(10, 'aaa组', null, null, null, 5, '0,1,2,5'); INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(11, 'aaa2组', null, null, null, 5, '0,1,2,5'); INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(12, 'aaa3组', null, null, null, 10, '0,1,2,5,10'); INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path]) VALUES(13, 'bbb组', null, null, null, 9, '0,1,4,9');3. 找到所有上级节点;with f as(select ID, Name,Parent,Path from Orgnization where[ID]='4'union allselect a.ID,, a.Parent, a.Path from Orgnization a join f b on a.[ID]=b.[Parent])select*from f order by Path asc4.找到所有下级节点这个相对复杂⼀点，这⾥采⽤的⽅式通过创建和调⽤SQL函数解决4.1 创建⼀个字符串拆分split函数，⽤于解析类似0,1,4这种csv格式/****** Object: UserDefinedFunction [dbo].[m_split] Script Date: ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate function[dbo].[m_split](@c varchar(2000),@split varchar(2))returns@t table(col varchar(200))asbeginwhile(charindex(@split,@c) <>0)begininsert@t(col) values (substring(@c,1,charindex(@split,@c)-1))set@c=stuff(@c,1,charindex(@split,@c),'')-- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))endinsert@t(col) values (@c)returnendGO4.2 创建⼀个⽤于在Path中匹配节点ID的函数/****** Object: UserDefinedFunction [dbo].[GetState] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate function[dbo].[GetState](@s1varchar(2000),@s2varchar(2000)) returns intasbegindeclare@i intdeclare@j intdeclare@k intselect@i=count(a.col) from dbo.m_split(@s1,',') aright join (select*from dbo.m_split(@s2,',')) bona.col=b.colwhere a.col is not nullselect@j=count(col) from dbo.m_split(@s1,',')where col is not nullif(@i=@j)set@k=0elseset@k=1return@kendGO4.3 执⾏查询语句select*from nization where dbo.GetState('4', Path) ='0'。

[SQLServer]树形结构的创建

[SQLServer]树形结构的创建对于SQL Server来说，构建显⽰⼀个树形结构不是⼀件容易的事情，逻辑构造能⼒不是它的强项。

不过也不是说它没有能⼒⼲这个事情，只要换⼀种思维⽅式就可以理解它的⼯作原理。

例如，现在有⼀张表的内容如下：CategoryNO CategoryName Parent---------- -------------------------------------------------- ------0 ROOT NULL1 .NET 02 DataBase 03 Java 04 Others 05 WindowsOS 06 F# 17 C# 18 WPF 19 110 SQL Server 211 J2SE 312 批处理 513 注册表 514 SliverLight 815 基本命令 1216 扩展命令 1217 HKLM 1318 HKCU 1319 DIR 1520 COPY 1521 DEL 1522 IE 523 LINQ 124 C++ 0它看上去是多么混乱⽆序，我们希望它能按如下⽅式显⽰，也就是所谓的树形结构：CategoryNO CategoryName----------- --------------------1 .NET6 F#7 C#8 WPF14 SliverLight9 23 LINQ2 DataBase10 SQL Server3 Java11 J2SE4 Others5 WindowsOS12 批处理15 基本命令19 DIR20 COPY21 DEL16 扩展命令13 注册表17 HKLM18 HKCU22 IE24 C++⾄少这样看上去好多了。

现在来看看如何实现这个功能。

⾸先我们需要⼀个变量来记录当前进⼊到树形结构的哪个级别，并把它设置为0，表⽰第⼀个级别；以及另⼀个变量来记录当前在对哪条记录操作。

DECLARE@CategoryNO int, @Level intSET@Level=0然后要建⽴两张临时表，第⼀张表⽤来存储待处理记录，第⼆张表存储最终的结果。

SqlServer：实现树形结构递归查询（无限极分类）

SqlServer：实现树形结构递归查询（⽆限极分类）SQL Server 2005开始，我们可以直接通过CTE来⽀持递归查询,CTE即公⽤表表达式公⽤表表达式（CTE），是⼀个在查询中定义的临时命名结果集将在from⼦句中使⽤它。

每个CTE仅被定义⼀次（但在其作⽤域内可以被引⽤任意次），并且在该查询⽣存期间将⼀直⽣存。

可以使⽤CTE来执⾏递归操作。

⼀、查询树状结构某节点下的所有⼦节点with cte_child(id,areaName,pid,level)as(--起始条件select id,areaName,pid,0as level from erp_areawhere id =1-- 优先列出第⼀节点查询条件union all--递归条件select a.id,a.areaName,a.pid,b.level+1from erp_area ainner joincte_child bon ( a.pid=b.id))select*from cte_child⼆、查询树状结构某节点(44)的上级所有根节点with cte_child(id,areaName,pid)as(--起始条件select id,areaName,pid from erp_areawhere id =44--列出⼦节点查询条件union all--递归条件select a.id,a.areaName,a.pidfrom erp_area ainner joincte_child b --执⾏递归on a.id=b.pid)select*from cte_child;。

SQL中树形分层数据的查询优化

SQL中树形分层数据的查询优化在数据查询中，从2008开始SQL Server提供了⼀个新的数据类型hierarchyid，专门⽤来操作层次型数据结构。

hierarchyid 类型对层次结构树中有关单个节点的信息进⾏逻辑编码的⽅法是：对从树的根⽬录到该节点的路径进⾏编码。

这种路径在逻辑上表⽰为⼀个在根之后被访问的所有⼦级的节点标签序列。

表⽰形式以⼀条斜杠开头，只访问根的路径由单条斜杠表⽰。

对于根以下的各级，各标签编码为由点分隔的整数序列。

⼦级之间的⽐较就是按字典顺序⽐较由点分隔的整数序列。

每个级别后⾯紧跟着⼀个斜杠。

因此斜杠将⽗级与其⼦级分隔开。

例如，以下是长度分别为 1 级、2 级、2 级、3 级和 3 级的有效 hierarchyid 路径：• /• /1/• /0.3.-7/• /1/3/• /0.1/0.2/在没有hierarchyid的⽇⼦⾥，我们通过CTE的⽅式来查询⽗以及全部的下级，但是，数据量多的情况下，CTE的⽅式将会变的很慢，后来，我们通过构造PATH的⽅式来加快速度。

那么，有了hierarchyid类型后，⾃然得使⽤hierarchyid了。

现在，通过⼀个实际的例⼦来看看hierarchyid的威⼒。

⼀：CTE⽅式WITH CTEGetChild AS(SELECT * FROM EL_anization WHERE ID='ecc43c7159924dca91e2916368f923f4' --and [State]=0 and AuditState=2 UNION ALL(SELECT A.* FROM EL_anization AS AINNER JOIN CTEGetChild AS B ON a.PARENTID=B.ID --and A.[State]=0 and A.AuditState=2))查询出来4489⾏，需要25S。

看来CTE⽅式已经到了不能容忍的地步，那么，现在，我们就⽤它来进⾏优化。

SQL处理多级分类,查询结果呈树形结构

SQL处理多级分类,查询结果呈树形结构这样处理的弊端是：如果数据量⼤，⼦分类很多，达到4级以上，这⽅法处理极端占⽤数据库连接池对性能影响很⼤。

如果⽤SQL下⾯的CTE递归处理的话，⼀次性就能把结果给查询出来，⽽且性能很不错⽐⽤程序处理（数据量很⼤的情况），临时表性能更好，更⽅便复制代码代码如下:with area as(select *,id px,cast(id as nvarchar(4000)) px2 from region where parentid=0union allselect a.*,b.px,b.px2+ltrim(a.region_id) from region a join area b on a.parentid=b.id)select * from area order by px,px2可以查询出结果—-所有分类及相应分类下⼦分类id title parentid1 ⼴东省 02 ⼴州 13 ⽩云区 24 深圳 15 湖南省 06 长沙 57 株洲 5复制代码代码如下:with area as(select * from region where parentid=1union allselect a.* from region a join area b on a.parentid=b.id)select * from area可以查询出结果—-指定分类及相应分类下⼦分类id title parentid1 ⼴东省 02 ⼴州 13 ⽩云区 2性能分析：对于⼀个3500条地区记录的数据表，其中有省，市，县3级查询⽤时要1秒，视觉上感觉有点点慢，但不影响数据量不⼤的分类，使⽤绝对⽆压⼒。

sql server组织树语法

sql server组织树语法SQL Server组织树语法在SQL Server数据库管理系统中，组织树（也称为层次结构）是一种常见的数据结构，用于表示具有层次关系的数据。

组织树通常用于组织机构、产品分类等场景，其中每个节点代表一个实体，节点之间通过父子关系进行连接。

组织树可以通过多种方式实现，其中一种常见的方式是使用递归查询和CTE（公共表表达式）来构建树结构。

本文将介绍如何使用SQL Server的语法来组织树结构。

1. 创建表格我们需要创建一个表格来存储组织树的数据。

假设我们要创建一个组织机构的树结构，包含以下字段：- ID：节点的唯一标识符- Name：节点的名称- ParentID：父节点的ID可以使用以下SQL语句创建组织树表格：```sqlCREATE TABLE OrganizationTree (ID INT PRIMARY KEY,Name VARCHAR(100),ParentID INT);```2. 插入数据接下来，我们需要向组织树表格中插入数据。

下面是一个示例插入语句：```sqlINSERT INTO OrganizationTree (ID, Name, ParentID) VALUES (1, '总公司', NULL),(2, '分公司A', 1),(3, '分公司B', 1),(4, '部门A1', 2),(5, '部门A2', 2),(6, '部门B1', 3),(7, '部门B2', 3);```在这个示例中，我们创建了一个包含总公司、两个分公司和四个部门的组织树。

3. 查询组织树现在我们可以使用递归查询和CTE来查询组织树结构。

下面是一个示例查询语句：```sqlWITH RecursiveCTE AS (SELECT ID, Name, ParentID, 0 AS LevelFROM OrganizationTreeWHERE ParentID IS NULLUNION ALLSELECT ot.ID, , ot.ParentID, rc.Level + 1FROM OrganizationTree otINNER JOIN RecursiveCTE rc ON ot.ParentID = rc.ID)SELECT ID, Name, LevelFROM RecursiveCTEORDER BY Level, ID;```在这个示例中，我们使用递归CTE来遍历组织树，并按照层次和节点ID排序结果。

SQLServer树形表非循环递归查询的实例详解

SQLServer树形表⾮循环递归查询的实例详解很多⼈可能想要查询整个树形表关联的内容都会通过循环递归来查...事实上在微软在SQL2005或以上版本就能⽤别的语法进⾏查询，下⾯是⽰例。

--通过⼦节点查询⽗节点WITHTREE AS(SELECT * FROM AreasWHERE id = 6 -- 要查询的⼦ idUNION ALLSELECT Areas.* FROM Areas, TREEWHERE TREE.PId = Areas.Id)SELECT Area FROM TREE--通过⽗节点查询⼦节点WITHTREE AS(SELECT * FROM AreasWHERE id = 7 -- 要查询的⼦ idUNION ALLSELECT Areas.* FROM Areas, TREEWHERE TREE.Id = Areas.PId)SELECT Area FROM TREE通过⼦节点查询⽗节点查询结果为：修改代码为--通过⼦节点查询⽗节点declare @area varchar(8000);WITHTREE AS(SELECT * FROM AreasWHERE id = 6 -- 要查询的⼦ idUNION ALLSELECT Areas.* FROM Areas, TREEWHERE TREE.PId = Areas.Id)select @area=isnull(@area,'')+Area from Tree order by idselect Area= @area则结果为：中国北京市丰台区根据以上可以将这段代码封装为⼀个存储过程-----存储过程，递归获取树形地区表字符串if exists (select * from sysobjects where name='SP_GetAreaStr')drop proc SP_GetAreaStrgocreate procedure SP_GetAreaStr@id intasdeclare @area varchar(8000)beginWITHTREE AS(SELECT * FROM AreasWHERE id = @id -- 要查询的⼦ idUNION ALLSELECT Areas.* FROM Areas, TREEWHERE TREE.PId = Areas.Id)select @area=isnull(@area,'')+Area from Tree order by idselect Area= @areaendgo--exec sp_helptext 'SP_GetAreaStr'--goexec SP_GetAreaStr 28go查询结果：中国安徽省宿州市灵璧县所⽤表结构：部分数据：以上所述是⼩编给⼤家介绍的SQL Server 树形表⾮循环递归查询的实例详解的相关知识，希望对⼤家有所帮助，如果⼤家有任何疑问欢迎给我留⾔，⼩编会及时回复⼤家的！。

sqlserver数据层级

sqlserver数据层级SQL Server是一种关系型数据库管理系统，支持各种数据库操作和查询。

其中，数据层级的理解与应用是数据库设计和数据查询的重要组成部分。

本文将分为三个部分来讲解SQL Server数据层级：定义数据层级、使用数据层级和优化数据层级。

第一部分：定义数据层级在SQL Server中，可以使用层次结构数据模型来定义数据层级。

层次结构是一种树形结构，其中每个节点代表一个数据项，而子节点和父节点之间有明确定义的关系。

在SQL Server中，可以使用两种不同的数据类型来表示层次结构。

一种是使用自引用关系，即在同一个表中使用外键引用自身。

另一种是使用递归表达式，即使用Common Table Expressions（CTE）来定义层次结构。

以下是使用自引用关系定义数据层级的示例：CREATE TABLE Employees(EmployeeID int PRIMARY KEY,Name varchar(50),ManagerID int REFERENCES Employees(EmployeeID));在上述示例中，Employees表包含EmployeeID和Name两个列，其中EmployeeID是主键。

ManagerID列是一个外键，引用了同一个表中的另一个EmployeeID。

第二部分：使用数据层级在SQL Server中，可以使用各种查询语句来使用数据层级。

以下是几个常用的示例：1. 查询某个节点的所有子节点：SELECT *FROM EmployeesSTART WITH EmployeeID = 1CONNECT BY PRIOR EmployeeID = ManagerID;在上述示例中，查询了EmployeeID为1的员工及其所有下属员工。

2. 查询某个节点的所有父节点：WITH EmployeeHierarchy AS(SELECT EmployeeID, Name, ManagerIDFROM EmployeesWHERE EmployeeID = 6UNION ALLSELECT e.EmployeeID, , e.ManagerIDFROM Employees eJOIN EmployeeHierarchy eh ON eh.ManagerID = e.EmployeeID)SELECT *FROM EmployeeHierarchy;在上述示例中，查询了EmployeeID为6的员工及其所有上级领导。

【IT专家】Sql Server中通用的遍历树形表,利用堆栈消除递规,深度优先

本文由我司收集整编，推荐下载，如有疑问，请与我司联系Sql Server中通用的遍历树形表,利用堆栈消除递规,深度优先FROMsysobjects WHEREname=‘pc_TraversalTree’ AND type=‘P’) DROPPROCEDUREpc_TraversalTree GO /* 存储过程描述Ding 通用的遍历树,深度优先*/ CREATEPROCEDUREpc_TraversalTree @rootIDint =-1 --要查询的一组,作为返回表的根,@SourceSQLText--数据源的查询语句,MeID,ParentID,Col,Col2列有顺序,@Splitcharnvarchar(50)=‘‘--显示的层次分隔符号AS SETNoCountON -- declare@topint --当栈顶指针set@top=0 createtable#tmpSource( AuotID intIDENTITY(1,1), MeID int, ParentID int, Col1 nvarchar(1000), Col2 nvarchar(1000) ) -- 行ID,上层行ID,描述1,描述2 exec(‘Insert#tmpSource(MeID,ParentID,Col1,Col2)’+@SourceSQL)createtable#tmpOutput( AutoIDintIDENTITY(1,1), ParentIDint, MeID int, [Level] int, Col1 nvarchar(1000), Col2 nvarchar(1000) ) createtable#Stack( AutoIDintIDENTITY(1,1), MeIDint, Parentint, [level]int ) --根结点进栈insert#Stack(MeID,[level])select@rootID,0 declare@StackCountint set@StackCount=1 --print’Rootis’+@root declare@lastint declare@currentParentint declare@mylevelint set@currentParent=@rootID if@rootID=-1 begin insert#Stack(MeID,Parent,[level])selectMeID,@top,0from#tmpSourcewhereParentIDISNULLorderbyMeIDdesc end while(@StackCount 0) begin --退栈,堆栈表的最后一行为栈顶--得到当前元素的ID,Parent,level select@last=Max(AutoID)from#Stack selecttop1@top=MeID,@currentParent=Parent,@myLevel=[level]from#StackwhereAutoI D=@last deletefrom#StackwhereAutoID=@last --添加行到输出表insert#tmpOutput(parentID,MeID,[level],Col1,Col2)selecttop1@currentParent,MeID,@myLevel ,REPLICATE(@SplitChar,@myLevel)+cast( Col1asnvarchar) ,REPLICATE(@SplitChar,@myLevel)+cast(Col2asnvarchar) from#tmpSourcewhereMeID=@top -- select*from#tmpSourcewhereMeID=@top --。