树结构数据查询

一 、sql server 2008  树结构数据查询:

        ;WITH MenuTree AS( 
        SELECT 1 as level, menu_id as id , mtext as name, url, 
        parent_id as parentId, status, creator as creatorId, 
        create_time as createdTime, last_modifier as lastModifierId,
         1 AS Generation,cast(menu_id as varchar(20)) as path FROM sys_pri_menu 
         WHERE PARENT_ID =0 
         UNION ALL 
         SELECT Generation + 1 as level, Menu.menu_id as id , 
         Menu.mtext as name, Menu.url, Menu.parent_id as parentId, 
         Menu.status, Menu.creator as creatorId, Menu.create_time as createdTime, 
         Menu.last_modifier as lastModifierId, Generation + 1,cast(MenuTree.path+'/'+cast(Menu.menu_id as varchar(20)) as varchar(20))
         FROM sys_pri_menu AS Menu INNER JOIN 
         MenuTree ON Menu.PARENT_ID = MenuTree.id where 2 > 1 and Menu.status = 0 )
         SELECT mt.* FROM MenuTree mt order by path 

         

        

 二、oracle树数据结构查询

select level, 
       t.menu_id as id , 
       t.mtext as name, 
       t.sub_system as subSystem, 
       t.url, 
       t.parent_id as parentId, 
       t.status, 
       t.creator as creatorId, 
       t.create_time as createdTime, 
       t.last_modifier as lastModifierId, 
       t.last_modified as lastModified 
       from sys_pri_menu t 
       where 2 > 1 and t.status = 0 and t.SUB_SYSTEM = 2 
   start with t.parent_id=0 connect by prior t.menu_id=t.parent_id