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

Question

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，根节点没有出现在数据中，因此保留当前出现的最顶级节点，但是其叶子结点都忽略
   
   
3. 尝试先将pid=0也就是根节点的数据先取出来，然后筛选，但是因为整个节点还有分支，貌似5.7不支持两个集合取交集

Answer 1

在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中出现多次，你可能需要修改这个存储过程以正确地处理这种情况。