Queries about recursion in SQL Server database are used

Metric Rapid Development Platform database supports SQL Server database, the way that we used to use the Oracle recursive query with the start with dept_id=1000 connect by prior dept_id=upper_id.
For example, our organization has a lot of subordinate agencies and departments, to find out where the login is located, and list all the institutions and departments. SQL Server has the following wording:

 with newtable as (select a.dept_id,a.dept_name,a.upper_id,a.tree_code,a.extend_type,a.dept _level,a.sort_order from  sa_dept_dict a where a.dept_id= Login User's Department idunion  Allselect b.dept_id,b.dept_name,b.upper_id,b.tree_code,b.extend_type,b.dept_level,b.sort_order from  sa_dept_dict b  inner join newtable c on b.dept_id=c.upper_ idwhere b.dept_type=2 or b.dept_type=6)  , newtable1 as (select a.dept_id, A.dept_name,a.upper_id,a.tree_code,a.extend_type,a.dept_level,a.sort_order from  sa_dept_dict  a ,  (Select  right (MAX (Tree_code), 4)  dept_id from newtable where  extend_type= ' Agency '  )  c where a.dept_id=c.dept_idunion allselect b.dept_id, B.dept_name,b.upper_id,b.tree_code,b.extend_type,b.dept_level,b.sort_order from sa_dept_dict b   inner join NEWTABLE1 C ON C.DEPT_ID=B.UPPER_IDWHERE B.DEPT_TYPE=2 OR B.DEPT_TYPE=6)  select dept_id,dept_name,upper_id,sort_order,extend_type,tree_code from newtable1

Original address: http://bbs.delit.cn/thread-153-1-1.html

Reprint please specify the source:

Author: metric Technology http://www.delit.cn

Queries about recursion in SQL Server database are used