Oracle 11GR2的递归WITH子查询方法

递归WITH子查询是Oracle 11GR2版本引入的一种新特性。可以帮助我们解决很多树形数据模型的查询问题。下面是本文的详细攻略。

什么是递归WITH子查询

递归WITH子查询是一种特殊的SQL语句形式，它能够以递归的方式访问一个自引用的查询（即一个查询结果集中的某个列或几列引用了同一表中的其它行）。

递归WITH子查询通常用于查询树形结构的数据，比如组织架构、家族关系等。通过递归WITH子查询，我们可以轻松地查询父节点、子节点、兄弟节点等信息。

递归WITH子查询的语法

递归WITH子查询的语法如下所示：

WITH recursive_query_name (column_list) AS (
  initial_query
  UNION [ALL]
  recursive_query
) SELECT * FROM recursive_query_name;

其中，

• recursive_query_name：递归查询的名称。
• column_list：递归查询返回的列列表，列名可以自行定义。
• initial_query：递归查询的初始查询，一般用来输出跟结点信息。
• UNION [ALL]：递归关键字，ALL表示保留重复结果。
• recursive_query：递归查询，该查询的结果集包含一个或多个基于先前结果集行的递归关系。
• SELECT：最终查询。

递归WITH子查询的实际应用

假如我们有一个员工表，包含员工编号、员工姓名、上级编号等字段。我们需要查询某个员工的所有上级，包括该员工自己。可以使用如下的递归WITH子查询：

WITH recursive_query_name (empno, ename, mgr) AS (
  SELECT empno, ename, mgr FROM emp WHERE empno = 7934
  UNION ALL
  SELECT e.empno, e.ename, e.mgr FROM emp e
  INNER JOIN recursive_query_name r ON r.mgr = e.empno
) SELECT * FROM recursive_query_name;

上述SQL语句中，

• 初始查询是SELECT empno, ename, mgr FROM emp WHERE empno = 7934，表示查询初始员工信息。
• 递归查询是SELECT e.empno, e.ename, e.mgr FROM emp e INNER JOIN recursive_query_name r ON r.mgr = e.empno，表示查询该员工的下一个上级。

执行上述SQL语句，得到的结果集包含了该员工的所有上级，包括该员工自己。

递归WITH子查询的实例说明

下面再给出一个实例说明。假如我们有一个无向图，要求求出从某个结点开始能达到的所有结点。我们可以使用如下的递归WITH子查询：

WITH RECURSIVE recursive_query_name (node_from, node_to, depth) AS (
  SELECT node_from, node_to, 1 FROM my_graph WHERE node_from = 'a'
  UNION
  SELECT g.node_from, g.node_to, r.depth + 1 from my_graph g
  INNER JOIN recursive_query_name r ON g.node_from = r.node_to
) SELECT DISTINCT * FROM recursive_query_name;

上述SQL语句中，

• 初始查询是SELECT node_from, node_to, 1 FROM my_graph WHERE node_from = 'a'，表示查询初始结点信息。
• 普通递归查询是SELECT g.node_from, g.node_to, r.depth + 1 from my_graph g INNER JOIN recursive_query_name r ON g.node_from = r.node_to，表示查询下一个结点信息。

执行上述SQL语句，得到的结果集包含了从结点a开始，能达到的所有结点信息（包括还没有被访问到的结点）。