数据库中的递归查询
最近工作中用到了递归查询,顺便做下备忘:
1. SQL2005中的CTE查询:
WITH CTE(AAAAA,BBBBB,CCCCC)
AS(
-- 查询ID为411321150的数据
SELECT id as AAAAA,parent as BBBBB,name as CCCCC
FROM RegionInfo WHERE id='456789150'
UNION ALL
-- 连接前面查询结果并进行递归
SELECT R.id as AAAAA, R.parent as BBBBB, R.name as CCCCC
FROM RegionInfo as R
INNER JOIN CTE C
ON C.BBBBB=R.id
)
SELECT AAAAA, BBBBB, CCCCC
FROM CT
原表中数据为:
查询结果为:
2.ORACLE中的递归:
SELECT menuid,parentsid,menuname
FROM AC_MENU
START WITH parentsid='operorgManage'
CONNECT BY PRIOR menuid=parentsid
原表中数据为:
查询结果为: