SQL reads all subordinate or all ancestors of the tree type

In programming or building, you may encounter situations where it is often necessary to read all of its superiors or all subordinates based on a node of the tree type. It is then bound to the tree-shaped part display.
In Oracle, you can use start with ... connect by prior ... To achieve.

The specific wording is:
Query Subordinates:

Select *  from  with dept_id=2170 by prior dept_id=order by Sort_ ORDER

Query Subordinates:

Select *  from  with dept_id=2170 by prior upper_id=order by Sort_ ORDER

It's a bit more complicated in SQL SERVER. Need to use with .... As ... Statement, which is also called the subquery section (subquery factoring), allows you to do many things, define a SQL fragment that will
is used by the entire SQL statement. Sometimes it is to make the SQL statement more readable, or it may be in different parts of union all as part of providing data.
Below we will use with Union to query the tree's superiors and subordinates:

Query Subordinates:

 withTree as(Select *  fromSa_dept_dictwheredept_id= . Union  All SelectA.*  fromSa_dept_dict asA,tree asBwhereb.dept_id=A.UPPER_ID)Select *  fromTreeOrder  bySort_order

Query Superiors:

 withTree as(Select *  fromSa_dept_dictwheredept_id= . Union  All SelectA.*  fromSa_dept_dict asA,tree asBwherea.dept_id=B.UPPER_ID)Select *  fromTreeOrder  bySort_order

Original source: http://bbs.delit.cn/thread-132-1-1.html
Reprint please specify the source:
Author: metric Technology http://www.delit.cn

SQL reads all subordinate or all ancestors of the tree type