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