Tree query and connect by in Oracle

• Tree query and connect by in Oracle

  Using connect by and start with to create a report similar to a tree is not difficult, as long as you follow the following basic principles:

  When using connect by, the order of each clause should be:
  Select
  From
  Where
  Start
  Connect
  Order
  

• Prior orders the report from the root to the leaf (if the prior column is a parent) or from the leaf to the root (if the prior column is a descendant ).
  
• The where clause can exclude individuals from the tree, but does not exclude their descendants (or the ancestor, if the prior column is a descendant ).
  
• The conditions in connect by (especially not equal to) Eliminate the individual and all its children (or the ancestor, depending on how to track the tree ).
  
• Connect by cannot be used together with tables in the where clause.

The following are examples:

1. Traverse from root to leaf
SELECT n_parendid, n_name, (LEVEL-1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL
Start with n_id = 0
Connect by n_parendid = PRIOR n_id;

2. Traverse from leaf to root
SELECT n_parendid, n_name, (LEVEL-1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL
Start with n_id = 300
Connect by n_id = PRIOR n_parendid;

3. exclude individuals, but not their children
SELECT n_parendid, n_name, (LEVEL-1), n_id
FROM navigation
WHERE n_parendid is not null and n_id! = 2
Start with n_id = 0
Connect by n_parendid = PRIOR n_id;

4. remove an individual and all its children
SELECT n_parendid, n_name, (LEVEL-1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL
Start with n_id = 0
Connect by n_parendid = PRIOR n_id AND n_id! = 2;

5. Change the display Sequence
SELECT n_parendid, n_name, (LEVEL-1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL
Start with n_id = 0
Connect by n_parendid = PRIOR n_id
Order by n_viewnum DESC;