sql sever 树的基础查询

首先我们有一张部门表

Mes_Bas_Depts  

create table Mes_Bas_Depts  

(

Dept_ID uniqueidentifier,

Dept_Number nvarchar（100）,

Dept_ParentId, uniqueidentifier

)

1.查询所有本节点下面的所有子节点（儿子孙子等）

with Depts_tree as 
( select * from dbo.Mes_Bas_Depts 
 where Dept_ID = '2E855E09-1940-4523-9668-0084A5F2E8A5' 
UNION all select a.* from dbo.Mes_Bas_Depts as a 
inner join Depts_tree as b on a.Dept_ParentId = b.Dept_ID )
select * from Depts_tree 

2.查询所有的顶级父节点

SELECT * FROM dbo.Mes_Bas_Depts WHERE Dept_ParentId='00000000-0000-0000-0000-000000000000'

因为默认值是‘00000000-0000-0000-0000-000000000000’设定的是guid，如果父id是空值的话就是 is null了。

3.查询本节点上面的说有父节点（父亲，祖父等）

with Depts_tree as 
( select * from dbo.Mes_Bas_Depts 
 where Dept_ID = '2E855E09-1940-4523-9668-0084A5F2E8A5' 
UNION all select a.* from dbo.Mes_Bas_Depts as a 
inner join Depts_tree as b on a.Dept_ID = b.Dept_ParentId )
select * from Depts_tree

4.查询本节点的顶级父节点

with Depts_tree as 
( select * from dbo.Mes_Bas_Depts 
 where Dept_ID = '2E855E09-1940-4523-9668-0084A5F2E8A5' 
UNION all select a.* from dbo.Mes_Bas_Depts as a 
inner join Depts_tree as b on a.Dept_ID = b.Dept_ParentId )
select * from Depts_tree WHERE Dept_ParentId='00000000-0000-0000-0000-000000000000'

5.查询本节点的子节点

select * from Mes_Bas_Depts WHERE Dept_ParentId='2E855E09-1940-4523-9668-0084A5F2E8A5'

6.查询本节点的父节点

SELECT  * FROM dbo.Mes_Bas_Depts WHERE Dept_ID=(
SELECT Dept_ParentId FROM dbo.Mes_Bas_Depts
 WHERE Dept_ID = '2E855E09-1940-4523-9668-0084A5F2E8A5')