国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

分層數(shù)據(jù)Hierarchical Data探索(2.鄰接表模型)

Scott / 913人閱讀

摘要：分層數(shù)據(jù)探索例如無限級分類多級菜單省份城市引言第一篇分層數(shù)據(jù)探索遞歸已經(jīng)介紹了分層數(shù)據(jù)以及使用遞歸算法實(shí)現(xiàn)了無限極分類，但是遞歸即浪費(fèi)時(shí)間，又浪費(fèi)空間內(nèi)存尤其是在數(shù)據(jù)量大的情況下效率顯著下降。

分層數(shù)據(jù)Hierarchical Data探索（例如：無限級分類、多級菜單、省份城市） 引言

第一篇 分層數(shù)據(jù)Hierarchical Data探索(1.遞歸)已經(jīng)介紹了分層數(shù)據(jù)以及使用遞歸算法實(shí)現(xiàn)了無限極分類，但是遞歸即浪費(fèi)時(shí)間，又浪費(fèi)空間（內(nèi)存）,尤其是在數(shù)據(jù)量大的情況下效率顯著下降。
那么，在MySQL中如何處理分層數(shù)據(jù)呢？下面我們來說一說數(shù)據(jù)模型鄰接表模型

分層數(shù)據(jù)Hierarchical Data探索(1.遞歸 recursion)

分層數(shù)據(jù)Hierarchical Data探索(2.鄰接表模型 Adjacency List Model)

分層數(shù)據(jù)Hierarchical Data探索(3.嵌套集合模型 Nested Set Model)

鄰接表模型(Adjacency List Model) 
更多 鄰接表模型（Adjacency List Model）的介紹請見:wiki

# 為了模擬，我們創(chuàng)建一個(gè)表category包含三個(gè)字段：id，title，和parent_id如下：
CREATE TABLE category (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  parent_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES category (id) 
    ON DELETE CASCADE ON UPDATE CASCADE
);

# 插入模擬數(shù)據(jù)
INSERT INTO category(title,parent_id) VALUES("Electronics",NULL);

INSERT INTO category(title,parent_id) VALUES("Laptops & PC",1);
 
INSERT INTO category(title,parent_id) VALUES("Laptops",2);
INSERT INTO category(title,parent_id) VALUES("PC",2);
 
INSERT INTO category(title,parent_id) VALUES("Cameras & photo",1);
INSERT INTO category(title,parent_id) VALUES("Camera",5);
 
INSERT INTO category(title,parent_id) VALUES("Phones & Accessories",1);
INSERT INTO category(title,parent_id) VALUES("Smartphones",7);
 
INSERT INTO category(title,parent_id) VALUES("Android",8);
INSERT INTO category(title,parent_id) VALUES("iOS",8);
INSERT INTO category(title,parent_id) VALUES("Other Smartphones",8);
 
INSERT INTO category(title,parent_id) VALUES("Batteries",7);
INSERT INTO category(title,parent_id) VALUES("Headsets",7);
INSERT INTO category(title,parent_id) VALUES("Screen Protectors",7);

select * from category;
+----+----------------------+-----------+
| id | title                | parent_id |
+----+----------------------+-----------+
|  1 | Electronics          |      NULL |
|  2 | Laptops & PC         |         1 |
|  3 | Laptops              |         2 |
|  4 | PC                   |         2 |
|  5 | Cameras & photo      |         1 |
|  6 | Camera               |         5 |
|  7 | Phones & Accessories |         1 |
|  8 | Smartphones          |         7 |
|  9 | Android              |         8 |
| 10 | iOS                  |         8 |
| 11 | Other Smartphones    |         8 |
| 12 | Batteries            |         7 |
| 13 | Headsets             |         7 |
| 14 | Screen Protectors    |         7 |
+----+----------------------+-----------+
14 rows in set (0.00 sec)

檢索根節(jié)點(diǎn)

SELECT * FROM category WHERE parent_id IS NULL;
+----+-------------+-----------+
| id | title       | parent_id |
+----+-------------+-----------+
|  1 | Electronics |      NULL |
+----+-------------+-----------+
1 row in set (0.00 sec)

檢索所有葉子節(jié)點(diǎn)

SELECT
    c1.id, c1.title
FROM
    category c1
        LEFT JOIN
    category c2 ON c2.parent_id = c1.id
WHERE
    c2.id IS NULL;
+----+-------------------+
| id | title             |
+----+-------------------+
|  3 | Laptops           |
|  4 | PC                |
|  6 | Camera            |
|  9 | Android           |
| 10 | iOS               |
| 11 | Other Smartphones |
| 12 | Batteries         |
| 13 | Headsets          |
| 14 | Screen Protectors |
+----+-------------------+
9 rows in set (0.00 sec)

檢索整棵樹的分層路徑

SELECT t1.title AS lev1, t2.title as lev2, t3.title as lev3, t4.title as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent_id = t1.id
LEFT JOIN category AS t3 ON t3.parent_id = t2.id
LEFT JOIN category AS t4 ON t4.parent_id = t3.id
WHERE t1.title = "Electronics";
-------------+----------------------+-------------------+-------------------+
| lev1        | lev2                 | lev3              | lev4              |
+-------------+----------------------+-------------------+-------------------+
| Electronics | Laptops & PC         | Laptops           | NULL              |
| Electronics | Laptops & PC         | PC                | NULL              |
| Electronics | Cameras & photo      | Camera            | NULL              |
| Electronics | Phones & Accessories | Smartphones       | Android           |
| Electronics | Phones & Accessories | Smartphones       | iOS               |
| Electronics | Phones & Accessories | Smartphones       | Other Smartphones |
| Electronics | Phones & Accessories | Batteries         | NULL              |
| Electronics | Phones & Accessories | Headsets          | NULL              |
| Electronics | Phones & Accessories | Screen Protectors | NULL              |
+-------------+----------------------+-------------------+-------------------+
9 rows in set (0.00 sec)

檢索單一指定路徑

SELECT t1.title AS lev1, t2.title as lev2, t3.title as lev3, t4.title as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent_id = t1.id
LEFT JOIN category AS t3 ON t3.parent_id = t2.id
LEFT JOIN category AS t4 ON t4.parent_id = t3.id
WHERE t1.title = "Electronics" AND t4.title = "iOS";
+-------------+----------------------+-------------+------+
| lev1        | lev2                 | lev3        | lev4 |
+-------------+----------------------+-------------+------+
| Electronics | Phones & Accessories | Smartphones | iOS  |
+-------------+----------------------+-------------+------+
1 row in set (0.00 sec)

以下遞歸公用表達(dá)式（CTE）檢索。請注意，MySQL 8.0以上版本，CTE功能已經(jīng)支持

CTE 查詢整棵樹

WITH RECURSIVE category_path (id, title, path) AS
(
  SELECT id, title, title as path
    FROM category
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.title, CONCAT(cp.path, " > ", c.title)
    FROM category_path AS cp JOIN category AS c
      ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;
+------+----------------------+----------------------------------------------------------------------+
| id   | title                | path                                                                 |
+------+----------------------+----------------------------------------------------------------------+
|    1 | Electronics          | Electronics                                                          |
|    5 | Cameras & photo      | Electronics > Cameras & photo                                        |
|    6 | Camera               | Electronics > Cameras & photo > Camera                               |
|    2 | Laptops & PC         | Electronics > Laptops & PC                                           |
|    3 | Laptops              | Electronics > Laptops & PC > Laptops                                 |
|    4 | PC                   | Electronics > Laptops & PC > PC                                      |
|    7 | Phones & Accessories | Electronics > Phones & Accessories                                   |
|   12 | Batteries            | Electronics > Phones & Accessories > Batteries                       |
|   13 | Headsets             | Electronics > Phones & Accessories > Headsets                        |
|   14 | Screen Protectors    | Electronics > Phones & Accessories > Screen Protectors               |
|    8 | Smartphones          | Electronics > Phones & Accessories > Smartphones                     |
|    9 | Android              | Electronics > Phones & Accessories > Smartphones > Android           |
|   10 | iOS                  | Electronics > Phones & Accessories > Smartphones > iOS               |
|   11 | Other Smartphones    | Electronics > Phones & Accessories > Smartphones > Other Smartphones |
+------+----------------------+----------------------------------------------------------------------+
14 rows in set (0.01 sec)

CTE 查詢指定子樹

查詢id為 7 的 Phone & Accessories 的子樹

WITH RECURSIVE category_path (id, title, path) AS
(
  SELECT id, title, title as path
    FROM category
    WHERE parent_id = 7
  UNION ALL
  SELECT c.id, c.title, CONCAT(cp.path, " > ", c.title)
    FROM category_path AS cp JOIN category AS c
      ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;
+------+-------------------+---------------------------------+
| id   | title             | path                            |
+------+-------------------+---------------------------------+
|   12 | Batteries         | Batteries                       |
|   13 | Headsets          | Headsets                        |
|   14 | Screen Protectors | Screen Protectors               |
|    8 | Smartphones       | Smartphones                     |
|    9 | Android           | Smartphones > Android           |
|   10 | iOS               | Smartphones > iOS               |
|   11 | Other Smartphones | Smartphones > Other Smartphones |
+------+-------------------+---------------------------------+
7 rows in set (0.01 sec)

CTE 查詢單個(gè)枝葉路徑

從底部 iOS 到 頂部 Electronics 的單個(gè)路徑

WITH RECURSIVE category_path (id, title, parent_id) AS
(
  SELECT id, title, parent_id
    FROM category
    WHERE id = 10 -- child node
  UNION ALL
  SELECT c.id, c.title, c.parent_id
    FROM category_path AS cp JOIN category AS c
      ON cp.parent_id = c.id
)
SELECT * FROM category_path;
+------+----------------------+-----------+
| id   | title                | parent_id |
+------+----------------------+-----------+
|   10 | iOS                  |         8 |
|    8 | Smartphones          |         7 |
|    7 | Phones & Accessories |         1 |
|    1 | Electronics          |      NULL |
+------+----------------------+-----------+
4 rows in set (0.00 sec)

CTE 計(jì)算每個(gè)節(jié)點(diǎn)的層級

根節(jié)點(diǎn)為 0，每個(gè)子節(jié)點(diǎn)等于父節(jié)點(diǎn)加 1

WITH RECURSIVE category_path (id, title, lvl) AS
(
  SELECT id, title, 0 AS lvl
    FROM category
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.title,cp.lvl + 1
    FROM category_path AS cp JOIN category AS c
      ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY lvl;
+------+----------------------+------+
| id   | title                | lvl  |
+------+----------------------+------+
|    1 | Electronics          |    0 |
|    2 | Laptops & PC         |    1 |
|    5 | Cameras & photo      |    1 |
|    7 | Phones & Accessories |    1 |
|    4 | PC                   |    2 |
|    6 | Camera               |    2 |
|    8 | Smartphones          |    2 |
|   12 | Batteries            |    2 |
|   13 | Headsets             |    2 |
|   14 | Screen Protectors    |    2 |
|    3 | Laptops              |    2 |
|   11 | Other Smartphones    |    3 |
|    9 | Android              |    3 |
|   10 | iOS                  |    3 |
+------+----------------------+------+
14 rows in set (0.00 sec)

刪除節(jié)點(diǎn)及其子節(jié)點(diǎn)

要刪除節(jié)點(diǎn)及其子節(jié)點(diǎn)，只需刪除節(jié)點(diǎn)本身，所有子節(jié)點(diǎn)將由 DELETE CASCADE 外鍵約束自動刪除
例如：要刪除Laptops & PC節(jié)點(diǎn)及其子節(jié)點(diǎn)

DELETE FROM category WHERE id = 2;

刪除節(jié)點(diǎn)并提升其子節(jié)點(diǎn)

首先，parent_id將節(jié)點(diǎn)的直接子節(jié)點(diǎn)更新為id新父節(jié)點(diǎn)的子節(jié)點(diǎn)。

然后，刪除該節(jié)點(diǎn)。

例如，要刪除 Smartphones 節(jié)點(diǎn)和更新 Android，iOS，Other Smartphones 節(jié)點(diǎn)：
兩個(gè)語句都應(yīng)該包含在一個(gè)事務(wù)中：

BEGIN;

UPDATE category 
SET 
    parent_id = 7 -- Phones & Accessories
WHERE
    parent_id = 5; -- Smartphones

DELETE FROM category 
WHERE 
    id = 8;
 
COMMIT;
參考資源

鏈接：http://mikehillyer.com/articl...

著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://specialneedsforspecialkids.com/yun/31520.html

相關(guān)文章

  • 分層數(shù)據(jù)Hierarchical Data探索(1.遞歸)

    摘要：分層數(shù)據(jù)探索例如無限級分類多級菜單省份城市引言什么是分層數(shù)據(jù)類似于樹形結(jié)構(gòu)，除了根節(jié)點(diǎn)和葉子節(jié)點(diǎn)外，所有節(jié)點(diǎn)都有一個(gè)父節(jié)點(diǎn)和一個(gè)或多個(gè)子節(jié)點(diǎn)。接下來我會先通過一般方法和遞歸方法來實(shí)現(xiàn)無限極分類，然后再通過兩種數(shù)據(jù)模型來談一談分層數(shù)據(jù)的處理。 分層數(shù)據(jù)Hierarchical Data探索（例如：無限級分類、多級菜單、省份城市） 引言 什么是分層數(shù)據(jù)？ 類似于樹形結(jié)構(gòu)，除了根節(jié)點(diǎn)和葉子節(jié)...

    yzd 評論0 收藏0

  • DeepMind 提出分層強(qiáng)化學(xué)習(xí)新模型 FuN，超越 LSTM

    摘要：實(shí)驗(yàn)蒙特祖瑪?shù)膹?fù)仇蒙特祖瑪?shù)膹?fù)仇是上最難的游戲之一。圖蒙特祖瑪?shù)膹?fù)仇的學(xué)習(xí)曲線在第一個(gè)房間中學(xué)習(xí)的子目標(biāo)的可視化呈現(xiàn)。結(jié)論如何創(chuàng)建一個(gè)能夠?qū)W習(xí)將其行為分解為有意義的基元，然后重新利用它們以更有效地獲取新的行為，這是一個(gè)長期存在的研究問題。 論文題目：分層強(qiáng)化學(xué)習(xí)的 FeUdal 網(wǎng)絡(luò)（FeUdal Networks for Hierarchical Reinforcement Learnin...

    dailybird 評論0 收藏0

  • 學(xué)習(xí)JavaScript數(shù)據(jù)結(jié)構(gòu)與算法 — 圖

    摘要：圖關(guān)聯(lián)矩陣在關(guān)聯(lián)矩陣表示的圖中，矩陣的行表示頂點(diǎn)，列表示邊。如圖，頂點(diǎn)數(shù)是，邊的數(shù)量是，用鄰接矩陣表示圖需要的空間是，而使用關(guān)聯(lián)矩陣表示圖需要的空間是。廣度優(yōu)先搜索算法數(shù)據(jù)結(jié)構(gòu)是隊(duì)列。深度優(yōu)先搜索算法數(shù)據(jù)結(jié)構(gòu)是棧。 定義 圖和散列表、二叉樹一樣，是一種非線性數(shù)據(jù)結(jié)構(gòu)。如圖1所示，圖由一系列頂點(diǎn)以及連接頂點(diǎn)的邊構(gòu)成。由一條邊連接在一起的頂點(diǎn)成為相鄰頂點(diǎn)，比如A和B、A和D是相鄰的，而A和...

    yiliang 評論0 收藏0

  • 算法第四版4.1-無向圖詳解

    摘要：樹是一副無環(huán)連通圖。互不相連的樹組成的集合稱為森林。表示無向圖的數(shù)據(jù)類型圖的基本操作的兩個(gè)構(gòu)造，得到頂點(diǎn)數(shù)和邊數(shù)，增加一條邊。該方法不符合第一個(gè)條件，上百萬個(gè)頂點(diǎn)的圖是很常見的空間不滿足。 四種重要的圖模型： 無向圖（簡單連接） 有向圖（連接有方向性） 加權(quán)圖（連接帶有權(quán)值） 加權(quán)有向圖（連接既有方向性又帶有權(quán)值） 無向圖 定義：由一組頂點(diǎn)和一組能夠?qū)蓚€(gè)頂點(diǎn)相連的邊組成。 特殊：...

    scola666 評論0 收藏0

發(fā)表評論

0條評論

最新活動
閱讀需要支付1元查看
<