单表查询树形结构的SQL语句

http://blog.sina.com.cn/s/blog_58844e710100g9lx.html

 

#从Root往树末梢递归 SELECT * FROM TBL_TEST start WITH id=1 connect BY prior id = pid 

 

 SELECT * FROM TBL_TEST start WITH id in (1,2,3) connect BY prior id = pid ; //以树的形式显示树形表的所有行（递归查询）

 

SELECT S.PRD_ID AS prdId, S.PRD_FLMC AS prdFlmc,S.PRD_FID FROM ( SELECT PRD_ID, PRD_FLMC, PRD_FID FROM T_PRODUCTS) S CONNECT BY PRIOR S.PRD_ID = S.PRD_FID  START WITH  S.PRD_FID is null  (以树的形式显示所有的数据)