mysql父子集查詢(根據父級查詢所有子集)

更新時間：2024年02月06日 14:59:48 作者：咕嚕咕?；?

某些場景可能需要維護一些有父子關系的數據,本文主要介紹了mysql父子集查詢(根據父級查詢所有子集),具有一定的參考價值,感興趣的可以了解一下

需求1：

根據pid查詢出其下的所有子集（比如，子集的子集的子集…）全部查詢出來。

首先，在表中按照父節(jié)點(parent_id)和id字段(id)的順序對數據進行排序，并將排序后的結果存入 org_query 子查詢中。
然后，使用變量 @id 以遞歸方式檢索所有與指定節(jié)點相關的所有子節(jié)點。該變量初始化為 pid，并在 WHERE 子句和 SET 子句中用于確定與指定節(jié)點相關的行，直到找到樹的底部節(jié)點為止。關鍵的查詢部分是 CONCAT(@id, ‘,’, id) 函數，它將每個查找到的子節(jié)點的id添加到 @id 變量中。其它一些細節(jié)如FIND_IN_SET 也保證了真正意義上的遞歸。
最后，使用 GROUP_CONCAT 函數將子節(jié)點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下所有的子集全部查詢出來，但是只在一列輸出，用,分割開來：

在這里插入圖片描述

需求2：

根據pid查詢出其下的所有子集（比如，子集的子集的子集…）全部查詢出來，但是需要作為集合列表展示，一個id作為一條數據：
這個sql使用時，需要區(qū)別mysql版本是5還是8！

首先，根據指定的節(jié)點ID，找到它在樹形結構中的所有子節(jié)點。
然后，將所有子節(jié)點的 ID 按照層級關系連接成一個字符串。例如, ‘1,2,3,6,7,8’ 表示節(jié)點6, 7和 8 是節(jié)點3的子節(jié)點，節(jié)點1，2和3是根節(jié)點(第一層節(jié)點)。
接下來，使用 GROUP_CONCAT 函數和字符串替換操作確定每個子節(jié)點所在的層數，并為每個節(jié)點分配一個行號,分配行號時需要使用 MySQL 的變量 (@rownum := @rownum + 1) 來生成唯一的行數。
最后，使用 SUBSTRING_INDEX 函數和整數參數 rn，確定每個子節(jié)點的位置，截取出每個子節(jié)點的ID，并將它們添加到 id_list 中。
最終輸出的結果是一個帶有列名為"id_list"的表，該表包含由指定節(jié)點的所有子節(jié)點的 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)

結果如下：

在這里插入圖片描述