SQL CTE查询所有下级部门/上级部门

创建表:

CREATE TABLE [dbo].[department](
 [ID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
 [department] [nvarchar](20) NULL,
 [pid] [decimal](18, 0) NULL 
)

根据指定部门的ID取得所有下级部门

CREATE PROCEDURE [dbo].[getChildDeptById] 
 @id INT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 -- SET NOCOUNT ON;

 WITH   dept
          AS ( SELECT   *
               FROM     dbo.department
               WHERE    pid = @id
               UNION ALL
               SELECT   d.*
               FROM     dbo.department d
                        INNER JOIN dept ON d.pid = dept.id
             )
    SELECT  *
    FROM    dept
END

根据指定部门的ID取得所有上级部门

CREATE PROCEDURE [dbo].[getParentDeptById] 
 @id INT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 -- SET NOCOUNT ON; 
 WITH   dept
          AS ( SELECT   dp.*
               FROM     dbo.department d INNER JOIN dbo.department dp ON d.pid=dp.ID
               WHERE    d.id = @id
               UNION ALL
               SELECT   d.*
               FROM     dbo.department d
                        INNER JOIN dept ON d.id = dept.pid
             )
    SELECT  *
    FROM    dept 
END