获取分部或者部门同级以及下级部门或者分部

---获取所有的下级部门 ，含自己（for sqlserver）
create function f_dept_cid(@id int) returns @t_level table(id int ,departmentname  varchar(100),level int)
as
begin
  declare @departmentname varchar(100),@level int
  set @level = 1
  insert into @t_level select @id ,@departmentname, @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id ,a.departmentname, @level
    from hrmdepartment a , @t_Level b
    where a.supdepid = b.id and b.level = @level - 1
  end
  return
end
go

select * from f_dept_cid(47)

（for oracle）
SELECT id,subcompanyname,supsubcomid FROM HRMSUBCOMPANY where canceled !='1' or canceled is null START WITH id='81' CONNECT BY PRIOR id=supsubcomid