sql中的树结构相关操作

最新推荐文章于 2025-03-08 17:59:57 发布

原创最新推荐文章于 2025-03-08 17:59:57 发布 · 681 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #insert #path #null #join #table

MSSQL 专栏收录该内容

39 篇文章

订阅专栏

本文介绍两种在SQL中实现树状结构数据查询的方法：一种通过递归查询构建路径，另一种利用视图结合递归公用表表达式（CTE）实现。这两种方法均适用于具有层次结构的数据表。

sql中的树结构相关操作

1.查询出树状结构，目前这种方法适用于只有一个根节点情况，如果有多个根节点，则有异常。

表tblDepartment结构如下：

列名	类型	允许为空
Department	nvarchar(50)	否
DepartmentID	nvarchar(50)	否
SuperiorDepartment	nvarchar(50)	是

查询语名如下：

DECLARE @t TABLE(SuperiorDepartment varchar(50),[Level] int,[Path] varchar(8000),Department nvarchar(50))

DECLARE @l int

SET @l=0

INSERT @t SELECT DISTINCT SuperiorDepartment,@l,RIGHT(SPACE(20)+isnull(SuperiorDepartment,''),20),Department

FROM tblDepartment a

WHERE NOT EXISTS(

SELECT * FROM tblDepartment WHERE DepartmentID=a.SuperiorDepartment)

WHILE @@ROWCOUNT>0

BEGIN

SET @l=@l+1

INSERT @t SELECT a.DepartmentID,@l,b.[Path]+RIGHT(SPACE(20)+a.DepartmentID,20),b.Department

FROM tblDepartment a,@t b

WHERE isnull(a.SuperiorDepartment,'')=isnull(b.SuperiorDepartment,'') AND b.[Level]=@l-1

END

SELECT SuperiorDepartment=CASE [Level] WHEN 0 THEN '' ELSE '|'+REPLICATE('-----',[Level]) END+isnull(SuperiorDepartment,'')+' '+Department,

[Level]

FROM @t

ORDER BY Path

2. 利用视图

注意：这里使用了msSql中特有的sql语法。

列名	类型	允许为空	备注
ID	int	否
Name	nvarchar(50)	否
PID	int	是	0和NULL表示根节点
IsValid	bit	否

CREATE VIEW [dbo].[view_WarehouseWith]

with warehouse as

(select ID,isnull(PID,0) as PID

,isnull(convert(varchar(max),ID),'0') as IDPath

,convert(varchar(max),[Name]) as NamePath

,IsValid

from tblWarehouseNumber (nolock)

where PID IS NULL

union all

select t.ID,t.PID

,convert(varchar(max),c.IDPath+'/'+Convert(varchar(Max),t.ID))

,convert(varchar(max),c.NamePath+'/'+t.Name)

,t.IsValid

from tblWarehouseNumber as t (nolock) inner join warehouse c

on t.PID=c.ID)

select *,ChildCount=(select count(*) from tblWarehouseNumber (nolock) where PID=warehouse.ID) from warehouse