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


转载于:https://www.cnblogs.com/apollokk/archive/2012/12/19/6713922.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值