很好用的mysql父子集查询SQL(根据父级查询所有子集)
作者:mmseoamin日期:2023-12-05

在表中一定要有id和pid,这样才能使用该sql。

需求1:

根据pid查询出其下的所有子集(比如,子集的子集的子集…)全部查询出来。

  1. 首先,在表中按照父节点(parent_id)和id字段(id)的顺序对数据进行排序,并将排序后的结果存入 org_query 子查询中。
  2. 然后,使用变量 @id 以递归方式检索所有与指定节点相关的所有子节点。该变量初始化为 pid,并在 WHERE 子句和 SET 子句中用于确定与指定节点相关的行,直到找到树的底部节点为止。关键的查询部分是 CONCAT(@id, ‘,’, id) 函数,它将每个查找到的子节点的id添加到 @id 变量中。其它一些细节如FIND_IN_SET 也保证了真正意义上的递归。
  3. 最后,使用 GROUP_CONCAT 函数将子节点ID综合成一个用逗号分隔的字符串(all_sub_ids),并输出结果表。
SELECT GROUP_CONCAT(id) AS all_sub_ids 
FROM (
  SELECT * FROM (
    SELECT id,parent_id 
    FROM 表
    ORDER BY parent_id, id
  ) org_query,
  (SELECT @id := 此处填写需要查询的PID) initialisation
  WHERE FIND_IN_SET(parent_id, @id) > 0
  AND @id := CONCAT(@id, ',', id)
) sub_query;

这样就把pid下所有的子集全部查询出来,但是只在一列输出,用,分割开来:

很好用的mysql父子集查询SQL(根据父级查询所有子集),在这里插入图片描述,第1张

需求2:

根据pid查询出其下的所有子集(比如,子集的子集的子集…)全部查询出来,但是需要作为集合列表展示,一个id作为一条数据:

这个sql使用时,需要区别mysql版本是5还是8!

  1. 首先,根据指定的节点ID,找到它在树形结构中的所有子节点。
  2. 然后,将所有子节点的 ID 按照层级关系连接成一个字符串。例如, ‘1,2,3,6,7,8’ 表示节点6, 7和 8 是节点3的子节点,节点1,2和3是根节点(第一层节点)。
  3. 接下来,使用 GROUP_CONCAT 函数和字符串替换操作确定每个子节点所在的层数,并为每个节点分配一个行号,分配行号时需要使用 MySQL 的变量 (@rownum := @rownum + 1) 来生成唯一的行数。
  4. 最后,使用 SUBSTRING_INDEX 函数和整数参数 rn,确定每个子节点的位置,截取出每个子节点的ID,并将它们添加到 id_list 中。
  5. 最终输出的结果是一个带有列名为"id_list"的表,该表包含由指定节点的所有子节点的 ID 组成的逗号分隔的列表。
5.7版本如下:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(all_sub_ids, ',', rn), ',', -1) AS id_list
FROM (
  SELECT GROUP_CONCAT(id) AS all_sub_ids, 
         ANY_VALUE(LENGTH(GROUP_CONCAT(id SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(id SEPARATOR ','), ',', '')) + 1) AS c,
         @rownum := @rownum + 1 AS rn
  FROM (
    SELECT * FROM (
      SELECT id,parent_id 
      FROM 表
      ORDER BY parent_id, id
    ) org_query,
    (SELECT @id := 此处填写需要查询的PID) initialisation
    WHERE FIND_IN_SET(parent_id, @id) > 0
    AND @id := CONCAT(@id, ',', id)
  ) sub_query,
  (SELECT @rownum := 0) r
  GROUP BY rn 
) ids;
8.0版本如下:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(all_sub_ids, ',', rn), ',', -1) AS id_list, c
FROM (
  SELECT GROUP_CONCAT(id) AS all_sub_ids, 
         LENGTH(GROUP_CONCAT(id SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(id SEPARATOR ','), ',', '')) + 1 AS c,
         @rownum := @rownum + 1 AS rn
  FROM (
    SELECT * FROM (
      SELECT id, parent_id 
      FROM 表
      ORDER BY parent_id, id
    ) org_query,
    (SELECT @id := 此处填写需要查询的PID) initialization
    WHERE FIND_IN_SET(parent_id, @id) > 0
    AND @id := CONCAT(@id, ',', id)
  ) sub_query,
  (SELECT @rownum := 0) r
  GROUP BY rn, c
) ids;

简化版:

上面的sql其实就是脱裤子放屁,哈哈哈哈

SELECT id
  FROM (
    SELECT id,parent_id 
    FROM process_bim_data 
    ORDER BY parent_id, id
  ) org_query,
  (SELECT @id := 452) initialisation
  WHERE FIND_IN_SET(parent_id, @id) > 0
  AND @id := CONCAT(@id, ',', id)

结果如下:

很好用的mysql父子集查询SQL(根据父级查询所有子集),在这里插入图片描述,第2张

随缘更新博客,分享自身实用文章。谢谢