sql中的树结构相关操作

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]

AS

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

GO