SQL Server recursive query (up query, down query)

- Recursively query all sub departments under a department (including the current department).

with temp(id,departmentname,departmentmark,subcompanyid,supdeptid,showorder,canceled,level,curLevel)
  as
  (
  -- --1: initial query (here PID= 0 is the lowest root node in the data 
  select
  id,departmentname,departmentmark,subcompanyid,supdeptid,showorder,canceled,level,1
  as
  level
  from HrmDepartment
  where id =  #{deptId,jdbcType=INTEGER}
  union all
  -- --2: recursive condition 
  select
  d.id,d.departmentname,d.departmentmark,d.subcompanyid,d.supdeptid,d.showorder,d.canceled,d.level,t.curLevel+1
  from HrmDepartment d -- --3: both the temporary table and the original data table here must use aliases, otherwise when recursively querying, it is not known which column of the table is being queried 
  inner join temp t on d.supdeptid=t.id --this correlation is very important, it is important to understand who is whose parent node 
  where d.canceled is null or d.canceled=0 )
  -- --4: after recursion is completed, make sure not to miss this query statement, otherwise an error will be reported 
  select * from temp
  ORDER BY showorder

- Recursively query all superior departments of a department (including the current department).

with temp(id,departmentname,departmentmark,subcompanyid,supdeptid,showorder,canceled,level)
  as
  (
  --1: initial query (here PID= 0 is the lowest root node in the data 
  select id,departmentname,departmentmark,subcompanyid,supdeptid,showorder,canceled,1 as level
  from HrmDepartment
  where id=#{deptId,jdbcType=INTEGER}
  union all
  --2: recursive condition 
  select d.id,d.departmentname,d.departmentmark,d.subcompanyid,d.supdeptid,d.showorder,d.canceled,t.level+1 from HrmDepartment d --3: both the temporary table and the original data table here must use aliases, otherwise when recursively querying, it is not known which column of the table is being queried 
  inner join temp t on d.id=t.supdeptid --this correlation is very important, it is important to understand who is whose parent node 
  where d.canceled is null or d.canceled=0)
  --4: after recursion is completed, make sure not to miss this query statement, otherwise an error will be reported 
  select * from temp
  order by level