mysql查询所有的上级部门_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