快捷導(dǎo)航

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

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

某些場(chǎng)景可能需要維護(hù)一些有父子關(guān)系的數(shù)據(jù),本文主要介紹了mysql父子集查詢(根據(jù)父級(jí)查詢所有子集),具有一定的參考價(jià)值,感興趣的可以了解一下

需求1：

根據(jù)pid查詢出其下的所有子集（比如，子集的子集的子集…）全部查詢出來(lái)。

首先，在表中按照父節(jié)點(diǎn)(parent_id)和id字段(id)的順序?qū)?shù)據(jù)進(jìn)行排序，并將排序后的結(jié)果存入 org_query 子查詢中。
然后，使用變量 @id 以遞歸方式檢索所有與指定節(jié)點(diǎn)相關(guān)的所有子節(jié)點(diǎn)。該變量初始化為 pid，并在 WHERE 子句和 SET 子句中用于確定與指定節(jié)點(diǎn)相關(guān)的行，直到找到樹(shù)的底部節(jié)點(diǎn)為止。關(guān)鍵的查詢部分是 CONCAT(@id, ‘,’, id) 函數(shù)，它將每個(gè)查找到的子節(jié)點(diǎn)的id添加到 @id 變量中。其它一些細(xì)節(jié)如FIND_IN_SET 也保證了真正意義上的遞歸。
最后，使用 GROUP_CONCAT 函數(shù)將子節(jié)點(diǎn)ID綜合成一個(gè)用逗號(hào)分隔的字符串(all_sub_ids)，并輸出結(jié)果表。

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 := 此處填寫(xiě)需要查詢的PID) initialisation
  WHERE FIND_IN_SET(parent_id, @id) > 0
  AND @id := CONCAT(@id, ',', id)
) sub_query;

這樣就把pid下所有的子集全部查詢出來(lái)，但是只在一列輸出，用,分割開(kāi)來(lái)：

在這里插入圖片描述

需求2：

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

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

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 := 此處填寫(xiě)需要查詢的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 := 此處填寫(xiě)需要查詢的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;

簡(jiǎn)化版：
上面的sql其實(shí)就是脫褲子放屁，哈哈哈哈

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)

結(jié)果如下：

在這里插入圖片描述