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