sql中的树结构相关操作

水深无声 2022-08-12 00:20 12阅读 0赞

sql中的树结构相关操作

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

表tblDepartment结构如下：

<table style="width: 347px; border-collapse: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm;"> 
 <tbody> 
 <tr style="height: 14.25pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes;"> 
 <td style="width: 113pt; height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 列名 </td> 
 <td style="width: 90pt; height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 类型 </td> 
 <td style="width: 57pt; height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 允许为空 </td> 
 </tr> 
 <tr style="height: 14.25pt; mso-yfti-irow: 1;"> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> Department&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> nvarchar(50) </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 否 </td> 
 </tr> 
 <tr style="height: 14.25pt; mso-yfti-irow: 2;"> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> DepartmentID&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> nvarchar(50)&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 否 </td> 
 </tr> 
 <tr style="height: 14.25pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes;"> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> SuperiorDepartment&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> nvarchar(50)&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 是 </td> 
 </tr> 
 </tbody> 
</table>

查询语名如下：

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

WHERENOT 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

ORDERBY Path

2.利用视图

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

<table style="width: 423px; border-collapse: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm;"> 
 <tbody> 
 <tr style="height: 14.25pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes;"> 
 <td style="width: 113.1pt; height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 列名 </td> 
 <td style="width: 90.1pt; height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 类型 </td> 
 <td style="width: 57.05pt; height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 允许为空 </td> 
 <td style="width: 57.05pt; height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 备注 </td> 
 </tr> 
 <tr style="height: 14.25pt; mso-yfti-irow: 1;"> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> ID&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> int </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 否 </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> &nbsp; </td> 
 </tr> 
 <tr style="height: 14.25pt; mso-yfti-irow: 2;"> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> Name&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> nvarchar(50)&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 否 </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> &nbsp; </td> 
 </tr> 
 <tr style="height: 14.25pt; mso-yfti-irow: 3;"> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> PID </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> int </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 是 </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 0和NULL表示根节点 </td> 
 </tr> 
 <tr style="height: 14.25pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes;"> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> IsValid&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> bit&nbsp; </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> 否 </td> 
 <td style="height: 14.25pt; background-color: transparent; border: #ece9d8; padding: 0cm;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

CREATEVIEW \[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)