数据库中递归循环mysql、oracle

Oracle中start with和connect by 用法理解

 

connect by 是结构化查询中用到的，其基本语法是：

1 select … from tablename
2 start with 条件1
3 connect by 条件2
4 where 条件3;

例：

1 select * from table
2 start with org_id = ‘HBHqfWGWPy’
3 connect by prior org_id = parent_id;

     简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:org_id，parent_id，那么通过表示每一条记录的parent是谁，就可以形成一个树状结构，用上述语法的查询可以取得这棵树的所有记录，其中：   

        条件1  是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
        条件2 是连接条件，其中用 PRIOR表示上一条记录，比如  CONNECT BY PRIOR org_id = parent_id；就是说 上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。
          条件3 是过滤条件，用于对返回的所有记录进行过滤。

 

理解START WITH和CONNECT BY
在层次型查询中，START WITH定义了层次结构的起点，即查询的根节点。而CONNECT BY则指定了层次间的关系，通常是父子关系。

这两个子句通常配合使用，以递归方式遍历和选择数据表中的记录。

使用START WITH
在使用START WITH时，需要指定递归开始的条件。这个条件通常是对某个字段的限定，用来选出作为查询起点的记录。

使用CONNECT BY
CONNECT BY子句定义了父记录和子记录之间的关系。在这个子句中，PRIOR关键字用于指明上一级的记录。
 

MySQL中的递归查询

MySQL中的递归查询主要通过WITH RECURSIVE语句来实现，这在处理具有层级关系或树形结构的数据时非常有用。下面将通过一个具体的例子来详细解释如何在MySQL中使用递归查询。

示例场景
假设我们有一个部门表（departments），其中包含部门的ID、部门名称以及上级部门的ID（parent_id）。现在，我们想要查询出某个部门及其所有下级部门的名称。

WITH RECURSIVE SubDeps AS (
-- 初始查询，选择顶级部门（技术部）
SELECT id, name, parent_id
FROM departments
WHERE name = '技术部'
UNION ALL
-- 递归查询，从当前已知部门中继续查询它们的下级部门
SELECT d.id, d.name, d.parent_id
FROM departments d
INNER JOIN SubDeps sd ON d.parent_id = sd.id
)
SELECT * FROM SubDeps;
 

WITH RECURSIVE：这个语句开始了一个递归的公用表表达式（Common Table Expression, CTE）。
SubDeps：这是CTE的名称，在递归查询中我们可以引用它。
初始查询：首先，我们从departments表中选择出顶级部门（这里是“技术部”）。
UNION ALL：我们使用UNION ALL来合并初始查询的结果和递归查询的结果。UNION ALL允许重复的行，如果确定不会有重复，也可以使用UNION（但在这个例子中，由于我们可能查询出多个层级的相同部门，所以使用UNION ALL）。
递归查询：在递归查询中，我们从departments表中再次选择数据，但这次我们选择的是那些其parent_id等于CTE中当前行的id的行。这样，我们就能找到所有下级部门。
SELECT * FROM SubDeps：最后，我们从CTE中选择所有结果。
 

 

使用递归CTE查询菜单层级

 

WITH RECURSIVE menu_cte AS (

-- 基础情况：选择顶级菜单项（parent_id IS NULL）

SELECT id, name, parent_id, 1 AS level

FROM menu

WHERE parent_id IS NULL

UNION ALL

-- 递归情况：选择所有子菜单项，并为它们增加层级深度

SELECT m.id, m.name, m.parent_id, mcte.level + 1

FROM menu m

INNER JOIN menu_cte mcte ON m.parent_id = mcte.id

)

SELECT * FROM menu_cte ORDER BY level, id;

在这个查询中：

• WITH RECURSIVE menu_cte AS (...) 定义了一个递归的公用表表达式。

• 第一个SELECT语句（基础情况）选择所有顶级菜单项。

• UNION ALL 后面的语句（递归情况）将所有子菜单项加入到结果中，并为每个子菜单项增加层级深度。

• ORDER BY level, id 确保结果按层级和ID排序，使得输出结果更易于理解。

这样，你就可以通过递归查询获取一个树状结构的菜单数据，包括每个菜单项的层级。