数据库递归查询

pacer123

数据库递归查询sql ，觉得在做目录树的结构应用时很有用处。可以根据父id查询相应的所有的递归子目录。 
       DB2:向下递归查询：
               with rpl(catalog_id,parent_id,catalog_name,catalog_level,region_code) as
     (
          select root.catalog_id,root.parent_id,root.catalog_name,root.catalog_level,root.region_code
         from sms_catalog root
          where parent_id = $catalogId$ and region_code = $regionCode$
          union all
          select child.catalog_id,child.parent_id,child.catalog_name,child.catalog_level,child.region_code
        from rpl parent,sms_catalog child
          where  parent.catalog_id = child.parent_id
     )
     SELECT  catalog_id as catalogId,
             parent_id as parentId,
             catalog_name as catalogName,
             catalog_level as catalogLevel,
             region_code as regionCode
         FROM rpl
     order by catalog_id,parent_id,catalog_name,catalog_level,region_code with ur 
 
     DB2:向上递归查询：很简单类似只需要更换sql中相应的对应关系即可（反向对应）
with rpl(catalog_id,parent_id,catalog_name,catalog_level,region_code) as
     (
          select root.catalog_id,root.parent_id,root.catalog_name,root.catalog_level,root.region_code
         from sms_catalog root
          where catalog_id = 33 and region_code = 917
          union all
          select child.catalog_id,child.parent_id,child.catalog_name,child.catalog_level,child.region_code
        from rpl parent,sms_catalog child
          where    child.catalog_id  = parent.parent_id
     )
     SELECT  catalog_id as catalogId,
             parent_id as parentId,
             catalog_name as catalogName,
             catalog_level as catalogLevel,
             region_code as regionCode
         FROM rpl order by catalogid
上面的sql可以作为参考，根据自己的需要改变表明字段名称即可。下次有空会把oracle的递归查询sql也写出来。方便使用