Connect by in Oracle implemented in SQL Server

1. In Oracle:

Select org_id

From organizations

Start with org_id=: org_id

Connect by Prior org_id=supervision_org_id

ORDER BY supervision_org_id

2. SQL Functiong:

Functionno Subfunctionno Subfunctiontype

Systemadmin Privilege 0

Systemadmin Subsysadmin 1 (represented by tree branch)

Subsysadmin subsys1 0 (Leaf)

Subsysadmin Subsys2 0

Subsysadmin User 1

User Userone 0

Please show the number of functions that this hierarchy outputs (you can use connect by to do this in Oracle)

Thanks for the first.

3. SQL Statement implementation:

Example:

Declare @a table (tc_id int,tc_pid int,tc_name varchar (200))

Insert @a VALUES (1, 0, ' China ')

Insert @a values (2,0, ' USA ')

Insert @a values (3,0, ' Canada ')

Insert @a values (4,1, ' Beijing ')

Insert @a values (5,1, ' Shanghai ')

Insert @a values (6,1, ' Jiangsu ')

Insert @a values (7,6, ' Suzhou ')

Insert @a values (8,7, ' Changshu ')

Insert @a values (9,6, ' Nanjing ')

Insert @a values (10,6, ' Wuxi ')

Insert @a values (11,2, ' New York ')

Insert @a values (12,2, ' San Francisco ')

Declare @tmp1 table (tc_id int,tc_pid int,tc_name varchar (), Lev Int)

Insert @tmp1 Select *,1 from @a where tc_id=1

While exists (select 1 to @a A, @tmp1 b where a.tc_pid=b.tc_id and a.tc_id not in (select tc_id from @tmp1))

Insert @tmp1 Select a.*,1 from @a A, @tmp1 b where a.tc_pid=b.tc_id and a.tc_id isn't in (select tc_id from @tmp1)

SELECT * FROM @tmp1

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .