MySQL递归查询案列 + 前端树结构递归算法List_Tree互转

后端

递归查询案例1-全部递归

MySQL可以通过使用递归查询来查询具有层次结构的数据，通常可以使用with recursive关键字来实现递归查询。下面是一个简单的示例，说明如何使用递归查询在具有层次结构的表中查询数据：

假设有一个表Employee，其中包含以下列：id，name，manager_id。其中manager_id是对另一个员工的引用，表示该员工的经理。

with recursive EmployeeTree AS (
  SELECT id, name, manager_id
  FROM Employee
  WHERE id = 1

  UNION ALL

  SELECT e.id, e.name, e.manager_id
  FROM Employee e
  JOIN EmployeeTree et ON e.id = et.manager_id
)
SELECT * FROM EmployeeTree;

此查询使用with recursive关键字创建了一个名为EmployeeTree的临时表，该表由两个查询组成：

第一个查询检索根员工（在本例中为ID 1）的数据。
第二个查询通过加入Employee表并使用JOIN连接到EmployeeTree表来递归查询子级数据。
这将继续递归，直到没有更多的子级为止。最后，使用SELECT语句检索EmployeeTree表中的所有行，以查看层次结构。

此查询将返回具有层次结构的结果集，其中包括根员工及其子级的所有员工。

递归查询-层次控制

with recursive cte(id, name, parent_id, level) AS (
  SELECT id, name, parent_id, 1
  FROM directory
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, c.level + 1
  FROM directory d
  INNER JOIN cte c ON d.parent_id = c.id
  WHERE c.level < 3
)
SELECT id, name, parent_id, level
FROM cte;

该语句中的 cte 是一个递归查询的公共表表达式，用来生成目录树结构数据。其中，第一个 SELECT 查询用于查询根节点（即 parent_id 为空的节点），并将其递归级别设置为1。第二个 SELECT 查询用于查询子节点，并将递归级别设置为当前级别加1。 WHERE 子句中的 c.level < 3 表示只查询递归深度小于3的节点。

最后的 SELECT 查询用于查询结果，并包括递归查询的深度信息。

这样，就可以使用递归查询实现查询前3层目录树结构数据的功能。

---

2023.5.7补充，with recursive 支持高版本，低版本不太支持。上面的案例中是我云服务器上的mysql是8.0,今天试了下本地mysql5.0，不支持语法

• 不过想了下，后端如果要做树结构的递归操作，表里面一般都是写好的数据，好像根本用不到递归，也可能是我的逻辑需求还没到这个地步…
  
• 工作中遇到的场景，倒是有一个，单表数据，不是树结构，没有指定父id，不过可以通过sql语法硬造父id。通过多次连表。缺点就是，菜单等级有几次就会 UNION 几次

      SELECT DISTINCT str1 as name, "0" as pId
      FROM demo 
      UNION
	  SELECT DISTINCT  str2 as name, str1 as pId
      FROM demo 
	  union
      SELECT   str3 as name, str2 as pId
      FROM demo 

前端

我在贴一下我的前端方法，公司项目使用layui，树结构数据支持不太好不能拿到list自己转，所以自己重新写了下数据转换相关的逻辑

数据转换算法程序

    //变量对象
    var dataObj = {
        treeList: [],   //接收树结构数据转为list、数组数据的变量集合
        cardsList: [],  //渲染卡片元素时，暂存的数据容器集合
        cardStatus: false, //控制空白卡片是否生成的变量开关
        treeNodes: [],   //由list转变为树形节点数据的容器集合
        propertlyList: [], //二级属性页面，用来确定属性的容器集合
        replaceTreeNode: {//树的子节点，转为list中间变量
            title: "",
            pId: ""
        },
	}





   /*
    ---------------------------------------------------
    将List集合数据转化树形结构
    ---------------------------------------------------
    树形结构 ：
    data: [{
            title: '优秀',
            children: [
                { title: '80 ~ 90' },
                { title: '90 ~ 100'}
            ]
        }, ......]
    */
    var listToTreeNode = function (list, pid) {
        var tree = [];
        for (let i = 0; i < list.length; i++) {
            var node = list[i];
            if (node.pId === pid) {
                let item = listToTreeNode(list, node.title);
                if (item.length > 0) {
                    node.children = item;
                }
                tree.push(node);
            }
        }
        return tree;
    };






    /*
    ---------------------------------------------------
    将树形结构转化为List集合数据(组件点击会传一个node节点过来，boolean是自己写的一个开关，控制是否转换父节点）
    ---------------------------------------------------
    参数一、勾选的节点对象
    参数二、boolean类型，判断是否将选中的父节点转为List
    */
    var treeNodeToList = function (treeNode, boolean) {
        var arrys = [];
        if (treeNode.hasOwnProperty("children")) {
            for (let i = 0; i < treeNode.children.length; i++) {
                arrys = arrys.concat(treeNodeToList(treeNode.children[i]));//合并两个数组
            }
            //判断是否将父节点也转为list
            if (boolean) {
                dataObj.replaceTreeNode.title = treeNode.title;
                dataObj.replaceTreeNode.pId = treeNode.pId;
                console.log(arrys);
                arrys.push(dataObj.replaceTreeNode);
                return arrys;
            }
            return arrys;
        } else {
            arrys.push(treeNode);
            return arrys;
        }
    }