创建数据库表：

USE [WechatSalesSystem]

GO

/****** Object: Table [dbo].[Employee] Script Date: 2016/12/13 14:41:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Employee](

[EmployeeId] [int] IDENTITY(1,1) NOT NULL,

[ManagerId] [int] NULL,

[FirstName] [nvarchar](50) NULL,

[LastName] [nchar](50) NULL,

[Job] [nvarchar](50) NULL,

[ParentId] [int] NULL,

CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED

(

[EmployeeId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

查询子节点语句：

WITH EmployeeTree

AS (SELECT *

FROM Employee

WHERE EmployeeId =3 --第一个查询作为递归的基点(锚点)

UNION ALL

SELECT Employee.* --第二个查询作为递归成员， 下属成员的结果为空时，此递归结束。

FROM

EmployeeTree INNER JOIN Employee ON EmployeeTree.EmployeeId = Employee.ParentId)

SELECT *

FROM EmployeeTree

查询父节点语句：

with EmployeeTree AS

(

SELECT *,0 level from Employee where EmployeeId=3 

UNION ALL 

SELECT Employee.*,level+1 from EmployeeTree

JOIN Employee on EmployeeTree.parentid= Employee.EmployeeId

)

SELECT * from EmployeeTree where level=2;

 

查找从子节点到定级节点的路径：

with EmployeeTree as

(

select EmployeeId,ManagerId,FirstName,ParentId,cast(EmployeeId as varchar(100)) as fulltypeid

from Employee where EmployeeId=2--子节点

union all

select

b.EmployeeId,b.ManagerId,b.FirstName,b.ParentId,

cast(a.fulltypeid+','+cast(b.EmployeeId as nvarchar(100)) as varchar(100)) as fulltypeid

from

EmployeeTree a,--子节点数据集

Employee b --父节点数据集

where a.ParentId=b.EmployeeId --子节点数据集.parendID=父节点数据集.ID

)

select * from EmployeeTree ;