Connect by in oracle is implemented in SQL server

1. 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

01. systemadmin Privilege 0

01. systemadmin subsysadmin 1 (indicating the master Branch)

Subsysadmin subsys1 0 (leaf)

Subsysadmin subsys2 0

Subsysadmin User 1

User UserOne 0

Please show the number of letters written by this limit (connect by can be used in oracle to implement this function)

I deleted it 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, 'America ')

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, 'newyork ')

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

Declare @ tmp1 table (TC_Id int, TC_PID int, TC_Name varchar (200), levint)

Insert @ tmp1 select *, 1 from @ a where tc_ID = 1

While exists (select 1 from @ 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, @ tmp1 B where a. tc_pid = B. tc_ID and a. tc_ID not in (select tc_ID from @ tmp1)

Select * from @ tmp1

Note: For more exciting articles, please follow the help houseProgramming TutorialTopic.