query - mysql select recursive parent

如果您的數據庫支持數組，則還可以將父系列或物化路徑實現為父數​​據標識的數組。

特別是使用Postgres，您可以使用集合運算符來查詢層次結構，並使用GIN索引獲得出色的性能。 這使得在單個查詢中找到父母，孩子和深度非常簡單。 更新也相當易於管理。

如果你好奇，我已經寫了一些使用物理路徑的數組 。

我正在使用PostgreSQL和我的層次結構的閉合表。 我有一個通用的存儲過程為整個數據庫：

CREATE FUNCTION nomen_tree() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
DECLARE
  old_parent INTEGER;
  new_parent INTEGER;
  id_nom INTEGER;
  txt_name TEXT;
BEGIN
-- TG_ARGV[0] = name of table with entities with PARENT-CHILD relationships (TBL_ORIG)
-- TG_ARGV[1] = name of helper table with ANCESTOR, CHILD, DEPTH information (TBL_TREE)
-- TG_ARGV[2] = name of the field in TBL_ORIG which is used for the PARENT-CHILD relationship (FLD_PARENT)
    IF TG_OP = 'INSERT' THEN
    EXECUTE 'INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT $1.id,$1.id,0 UNION ALL
      SELECT $1.id,ancestor_id,depth+1 FROM ' || TG_ARGV[1] || ' WHERE child_id=$1.' || TG_ARGV[2] USING NEW;
    ELSE                                                           
    -- EXECUTE does not support conditional statements inside
    EXECUTE 'SELECT $1.' || TG_ARGV[2] || ',$2.' || TG_ARGV[2] INTO old_parent,new_parent USING OLD,NEW;
    IF COALESCE(old_parent,0) <> COALESCE(new_parent,0) THEN
      EXECUTE '
      -- prevent cycles in the tree
      UPDATE ' || TG_ARGV[0] || ' SET ' || TG_ARGV[2] || ' = $1.' || TG_ARGV[2]
        || ' WHERE id=$2.' || TG_ARGV[2] || ' AND EXISTS(SELECT 1 FROM '
        || TG_ARGV[1] || ' WHERE child_id=$2.' || TG_ARGV[2] || ' AND ancestor_id=$2.id);
      -- first remove edges between all old parents of node and its descendants
      DELETE FROM ' || TG_ARGV[1] || ' WHERE child_id IN
        (SELECT child_id FROM ' || TG_ARGV[1] || ' WHERE ancestor_id = $1.id)
        AND ancestor_id IN
        (SELECT ancestor_id FROM ' || TG_ARGV[1] || ' WHERE child_id = $1.id AND ancestor_id <> $1.id);
      -- then add edges for all new parents ...
      INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT child_id,ancestor_id,d_c+d_a FROM
        (SELECT child_id,depth AS d_c FROM ' || TG_ARGV[1] || ' WHERE ancestor_id=$2.id) AS child
        CROSS JOIN
        (SELECT ancestor_id,depth+1 AS d_a FROM ' || TG_ARGV[1] || ' WHERE child_id=$2.' 
        || TG_ARGV[2] || ') AS parent;' USING OLD, NEW;
    END IF;
  END IF;
  RETURN NULL;
END;
$_$;

然後對於我有層次結構的每個表，我創建一個觸發器

CREATE TRIGGER nomenclature_tree_tr AFTER INSERT OR UPDATE ON nomenclature FOR EACH ROW EXECUTE PROCEDURE nomen_tree('my_db.nomenclature', 'my_db.nom_helper', 'parent_id');

為了填充現有層次結構中的閉包表，我使用這個存儲過程：

CREATE FUNCTION rebuild_tree(tbl_base text, tbl_closure text, fld_parent text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    EXECUTE 'TRUNCATE ' || tbl_closure || ';
    INSERT INTO ' || tbl_closure || ' (child_id,ancestor_id,depth) 
        WITH RECURSIVE tree AS
      (
        SELECT id AS child_id,id AS ancestor_id,0 AS depth FROM ' || tbl_base || '
        UNION ALL 
        SELECT t.id,ancestor_id,depth+1 FROM ' || tbl_base || ' AS t
        JOIN tree ON child_id = ' || fld_parent || '
      )
      SELECT * FROM tree;';
END;
$$;

閉包表用3列定義 - ANCESTOR_ID，DESCENDANT_ID，DEPTH。 有可能（甚至建議）為ANCESTOR和DESCENDANT存儲具有相同值的記錄，深度值為零。 這將簡化對層次結構進行檢索的查詢。 他們確實很簡單：

-- get all descendants
SELECT tbl_orig.*,depth FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth <> 0;
-- get only direct descendants
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth = 1;
-- get all ancestors
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON ancestor_id = tbl_orig.id WHERE descendant_id = XXX AND depth <> 0;
-- find the deepest level of children
SELECT MAX(depth) FROM tbl_closure WHERE ancestor_id = XXX;

此設計尚未提及：

多譜系列

雖然它有局限性，但如果你能承受它，它非常簡單而且非常高效。 特徵：

• 列：每個血統級別的一個，指的是所有的父母直到根，當前項目的水平以下的級別被設置為NULL
• 限制層次結構的深度
• 廉價的祖先，後代，水平
• 廉價的插入，刪除，移動的葉子
• 昂貴的插入，刪除，移動內部節點

這裡有一個例子 - 鳥類的分類樹，所以層次結構是類別/順序/家族/屬/物種 - 物種是最低級別，1行= 1分類群（對應於葉節點的物種）：

CREATE TABLE `taxons` (
  `TaxonId` smallint(6) NOT NULL default '0',
  `ClassId` smallint(6) default NULL,
  `OrderId` smallint(6) default NULL,
  `FamilyId` smallint(6) default NULL,
  `GenusId` smallint(6) default NULL,
  `Name` varchar(150) NOT NULL default ''
);

和數據的例子：

+---------+---------+---------+----------+---------+-------------------------------+
| TaxonId | ClassId | OrderId | FamilyId | GenusId | Name                          |
+---------+---------+---------+----------+---------+-------------------------------+
|     254 |       0 |       0 |        0 |       0 | Aves                          |
|     255 |     254 |       0 |        0 |       0 | Gaviiformes                   |
|     256 |     254 |     255 |        0 |       0 | Gaviidae                      |
|     257 |     254 |     255 |      256 |       0 | Gavia                         |
|     258 |     254 |     255 |      256 |     257 | Gavia stellata                |
|     259 |     254 |     255 |      256 |     257 | Gavia arctica                 |
|     260 |     254 |     255 |      256 |     257 | Gavia immer                   |
|     261 |     254 |     255 |      256 |     257 | Gavia adamsii                 |
|     262 |     254 |       0 |        0 |       0 | Podicipediformes              |
|     263 |     254 |     262 |        0 |       0 | Podicipedidae                 |
|     264 |     254 |     262 |      263 |       0 | Tachybaptus                   |

這非常棒，因為只要內部類別不會在樹中改變它們的級別，您就可以非常簡單的方式完成所有需要的操作。

這真是一個方形釘，圓孔問題。

如果關係數據庫和SQL是您已經或願意使用的唯一錘子，那麼到目前為止發布的答案已經足夠。 但是，為什麼不使用旨在處理分層數據的工具？ 圖形數據庫非常適合複雜的分層數據。

與圖形數據庫解決方案可以輕鬆解決相同問題相比，關係模型的低效率以及將圖形/分層模型映射到關係模型上的任何代碼/查詢解決方案的複雜性都不值得。

考慮將材料清單作為常見的分層數據結構。

class Component extends Vertex {
    long assetId;
    long partNumber;
    long material;
    long amount;
};

class PartOf extends Edge {
};

class AdjacentTo extends Edge {
};

兩個子程序集之間的最短路徑 ：簡單圖遍曆算法。 可接受的路徑可以根據標准進行限定。

相似性 ：兩個程序集之間的相似程度是多少？ 對兩個子樹執行遍歷，計算兩個子樹的交集和並集。 百分比類似的是十字路口除以工會。

傳遞閉包 ：步行子樹並總結感興趣的領域，例如“多少鋁在一個子組件中？”

是的，您可以使用SQL和關係數據庫解決問題。 但是，如果您願意使用正確的工具進行工作，則有更好的方法。