Sql server 获得某一部门下的所有子部门。根据子部门获得它的上级部门。

kaywang

Sql server 获得某一部门下的所有子部门。根据部门获得它的上级部门。以为要用递归呢，原来不需要的，通过自定义函数里，使用个临时表就可以了。@@RowCount作用可真不小啊。

一、准备数据


--用户表
if object_id ('Users','u') is not null
    drop table Users
create table Users
(
    User_Id int identity(1,1) not null PRIMARY KEY,
    User_Name varchar(20)
)
set IDENTITY_INSERT  Users on
insert Users(User_Id,User_Name)
    select 1,'User1' union all
    select 2,'User2' union all
    select 3,'User3' union all
    select 4,'User4' union all
    select 5,'User5' union all
    select 6,'User6' union all
    select 7,'User7' union all
    select 8,'User8' union all
    select 9,'User9' union all
    select 10,'User10' union all
    select 11,'User11'
set identity_insert Users off

--部门表
if object_id ('Dept','u') is not null
    drop table Dept
create table Dept
(
    Dept_Id int identity(1,1) not null PRIMARY KEY,
    Dept_Name varchar(20),
    ParentDept_Id int
)
set IDENTITY_INSERT  Dept on
insert Dept(Dept_Id,Dept_Name,ParentDept_id)
    select 1,'Dept1',0 union all
    select 2,'Dept2',0union all
    select 3,'Dept3',0 union all
    select 4,'Dept1_1',1 union all
    select 5,'Dept1_2',1 union all
    select 6,'Dept3_1',3 union all
    select 7,'Dept3_1_1',6 union all
    select 8,'Dept3_1_2',6 union all
    select 9,'Dept3_1_3',6
set identity_insert Dept off


--用户部门表
if object_id ('UserDept','u') is not null
    drop table UserDept
create table UserDept
(
    UserDept_Id int identity(1,1) not null PRIMARY KEY,
    Dept_Id int,
    User_Id int
)
set IDENTITY_INSERT  UserDept on
insert UserDept(UserDept_Id,Dept_Id,User_Id)
    select 1,2,1 union all
    select 2,2,2union all
    select 3,1,5 union all
    select 4,3,3 union all
    select 5,3,4 union all
    select 6,9,11 union all
    select 7,9,10 union all
    select 8,9,8 union all
    select 9,4,6  union all
    select 10,7,7  union all
    select 11,4,9  
set identity_insert UserDept off

二、根据部门IP获得它下面的所有部门

if object_id('UF_GetChildDept','tf') is not null
drop function UF_GetChildDept
go
-- =============================================
-- Author:         adandelion
-- Create date: 2007-12-03
-- Description:     根据传入的部门ID，返回它下面的所有子部门。
-- =============================================
create function UF_GetChildDept( @DeptId int )
    returns  @tb table (id int)
as
begin
    insert into @tb
    select dept_id from dept where parentdept_id = @deptid
    while @@Rowcount >0  --只要有下级节点就循环
    begin
        insert into @tb
        select dept_id   --取出刚刚插入的deptid,去部门表里找parentdept_id = deptid的记录。
            from dept as a inner join @tb as b on a.parentdept_id = b.id and a.dept_id not in(select id from @tb)
    end
    return
end
go

select *
from dbo.UF_GetChildDept(7)
三、根据部门IP获得它的上级部门

if object_id('UF_GetParentDept','tf') is not null
drop function UF_GetParentDept
go
-- =============================================
-- Author:         adandelion
-- Create date: 2007-12-03
-- Description:     根据传入的部门ID，返回它的上级部门。
-- =============================================
create function UF_GetParentDept( @DeptId int )
    returns  @tb table (id int)
as
begin
    insert into @tb
    select parentdept_id from dept where dept_id = @deptid
    while @@Rowcount >0  --
    begin
        insert into @tb
        select parentdept_id   --
            from dept as a inner join @tb as b on a.dept_id = b.id and a.parentdept_id not in(select id from @tb)
    end
    return
end
go

select *
from dbo.UF_GetParentDept(7)