数据库递归查询
数据库递归查询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也写出来。方便使用
页:
[1]