问答博客标签用户活动
logo极客观点logo研发管理logoHarmonyOS
javascript
php
java
vue.js
git
html5
python
node.js
android
mysql
react.js
linux
ios
css
golang
spring
docker
程序员
logoONES 研发管理logo思否企业问答logo安谋科技 XPU

mysql树形结构数据筛选，叶子结点处理，如何筛选出预期数据？

头像
hurryUp
    161526

    mysql树形结构筛选
    mysql版本：5.7
    有如下结构：

    CREATE TABLE tb_tree (  
    id INT NOT NULL PRIMARY KEY,  
    pid INT not null default 0,  
    full_path VARCHAR(255) NULL,  
    FOREIGN KEY (pid) REFERENCES tb_tree(id)  
);

    id:主键ID
    pid：树形结构，标识当前行的父节点ID
    full_path：当前行在树形结构中的路径，例如当前id为10，父节点为9，那么他的full_path为：9-10

    例如在经过某一轮筛选后，有如下数据，或者说某个账号只能看到如下数据

    注意ID为100的数据，它的父节点99是存在的，只是业务逻辑中无法得到该节点的数据
    INSERT INTO tb_tree (id, pid, full_path) VALUES (1, 0, '1');
INSERT INTO tb_tree (id, pid, full_path) VALUES (2, 1, '1-2');
INSERT INTO tb_tree (id, pid, full_path) VALUES (3, 2, '1-2-3');
INSERT INTO tb_tree (id, pid, full_path) VALUES (4, 3, '1-2-3-4');
INSERT INTO tb_tree (id, pid, full_path) VALUES (100, 99, '99-100');
INSERT INTO tb_tree (id, pid, full_path) VALUES (101, 99, '99-100-101');
INSERT INTO tb_tree (id, pid, full_path) VALUES (201, 200, '200-201');

    现在有如下需求：

    1. 如果存在根节点的数据，那么这个根节点的所有叶子结点都忽略，只保留根节点，如上面id为1，2，3，4的数据，因为2，3，4都是1的叶子结点，因此只筛选出id为1的数据
    2. id为100，101，根节点没有出现在数据中，因此保留当前出现的最顶级节点，但是其叶子结点都忽略
      image.png
      image.png
    3. 尝试先将pid=0也就是根节点的数据先取出来，然后筛选，但是因为整个节点还有分支，貌似5.7不支持两个集合取交集
    mysqltree树形结构
    阅读 710
    avatarAI BotBETA

    在MySQL 5.7中，要解决这个问题，我们可以使用递归查询。由于MySQL 5.7不支持公共表表达式（CTE），我们需要使用一个存储过程来实现递归。以下是一个可能的解决方案：

    首先，我们需要创建一个存储过程来处理这个问题。这个存储过程将遍历树形结构，找到所有的根节点，然后找到每个根节点的所有叶子节点。然后，我们可以从这些叶子节点中筛选出我们需要的节点。

    DELIMITER //
CREATE PROCEDURE GetTreeNodes(IN root_id INT)
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM tb_tree WHERE pid = root_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    -- 循环遍历每个子节点
    read_loop: LOOP
        FETCH cur INTO cur_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 如果当前节点是叶子节点，则输出其信息
        IF (SELECT COUNT(*) FROM tb_tree WHERE full_path LIKE CONCAT(root_id, '-%') AND pid != root_id) = 0 THEN
            SELECT * FROM tb_tree WHERE id = cur_id;
        ELSE
            -- 如果当前节点不是叶子节点，则递归处理其子节点
            CALL GetTreeNodes(cur_id);
        END IF;
    END LOOP;

    -- 关闭游标
    CLOSE cur;
END //
DELIMITER ;

    然后，你可以通过调用这个存储过程来获取你需要的节点。例如，如果你想获取根节点为1的所有节点，你可以这样做：

    CALL GetTreeNodes(1);

    这个存储过程将返回所有根节点为1的叶子节点。注意，这个解决方案假设每个节点在full_path中只出现一次。如果一个节点可能在full_path中出现多次，你可能需要修改这个存储过程以正确地处理这种情况。

    1 个回答
    得票最新
    头像
    MartinDai
      1.5k2830

      为什么一定要通过sql来实现，这种属于业务逻辑的，用代码来做不是更好维护

      撰写回答
      你尚未登录，登录后可以
      • 和开发者交流问题的细节
      • 关注并接收问题和回答的更新提醒
      • 参与内容的编辑和改进，让解决方法与时俱进
      推荐问题
      宣传栏