SQL如何實現查詢節點依賴

c-xuan發表於2019-02-27
SQL

SQL如何運用遞迴查詢有向無環圖的節點依賴呢？
在這裡插入圖片描述

問題是這樣的

例如我有以上的兩個圖，節點b的前置節點是a,節點c的前置節點是b和d，以此類推。給出一個節點，需要找出以此節點為前置節點的所有鏈條節點。比如給出a，那出來的結果就是a,b,c,d,e,給出f,那出來的結果就是f,g,h,i,j,k。

資料準備

建立表以及插入資料，多個前置節點用,隔開，注意，使用的是PostgreSQL資料庫。

-- ----------------------------
-- Table structure for mytest
-- ----------------------------
DROP TABLE IF EXISTS "public"."mytest";
CREATE TABLE "public"."mytest" (
"node" varchar(256) COLLATE "default",
"pre_nodes" varchar(10240) COLLATE "default"
);

-- ----------------------------
-- Records of mytest
-- ----------------------------
INSERT INTO "public"."mytest" VALUES ('a', null);
INSERT INTO "public"."mytest" VALUES ('b', 'a');
INSERT INTO "public"."mytest" VALUES ('c', 'b,d');
INSERT INTO "public"."mytest" VALUES ('d', 'b');
INSERT INTO "public"."mytest" VALUES ('e', 'c');
INSERT INTO "public"."mytest" VALUES ('f', null);
INSERT INTO "public"."mytest" VALUES ('g', 'f');
INSERT INTO "public"."mytest" VALUES ('h', 'g');
INSERT INTO "public"."mytest" VALUES ('i', 'g');
INSERT INTO "public"."mytest" VALUES ('j', 'g');
INSERT INTO "public"."mytest" VALUES ('k', 'h,i,j');

在這裡插入圖片描述

SQL建立

方法有多種，用函式或者儲存過程也可以，這裡使用WITH遞迴，相關資訊可以百度:postgresql 遞迴。第一個select語句中要給出起始節點。

WITH RECURSIVE graph_depds AS (
  SELECT node,pre_nodes FROM mytest a WHERE node = 'f'
  UNION ALL
  SELECT a.node,a.pre_nodes FROM mytest a, graph_depds b WHERE strpos(a.pre_nodes, b.node) > 0
)
SELECT DISTINCT node,pre_nodes FROM graph_depds ORDER BY node;

在這裡插入圖片描述

如果想向前查詢呢

上面的查詢相當於找出一個節點所有影響到的節點，其實就是向下遞迴，如果給出一個節點，想從這個節點往前追溯有關的節點呢？例如給出c節點，結果是a,b,c,d,其實就是向上遞迴，可以像下面這樣寫。

WITH RECURSIVE graph_depds AS (
  SELECT node,pre_nodes FROM mytest a WHERE node = 'i'
  UNION ALL
  SELECT a.node,a.pre_nodes FROM mytest a, graph_depds b WHERE strpos(b.pre_nodes,a.node) > 0
)
SELECT DISTINCT node,pre_nodes FROM graph_depds ORDER BY node;

在這裡插入圖片描述

其他資料庫

如果像Sql Server等支援WITH語法的資料，以上寫法可以移植，稍作修改就好。但是mysql等資料庫中的實現方式不一樣，百度mysql 遞迴相關的內容都能找到方法。

更多

相關文章