在mysql表中查找父子回圈

我在 MySQL 中有一個父子關系表，它包含以下列：id、pid、cid、value。像這樣創建：

create table PCRELATIONS(
 id INT NOT NULL AUTO_INCREMENT, 
 pid char(1) NOT NULL, 
 cid char(1) NOT NULL, 
 value varchar(10),   
 PRIMARY KEY ( id ) 
);

這里，id是代理鍵，pid并cid從另一臺FKS，并value為varchar。我知道 pid 和 cid 應該是 int 但我在這里使用 char 只是為了更容易地將問題可視化，而不會由于過多的 1 和 2 而造成混亂。

我需要找到表中恰好涉及三個節點的所有回圈，即 A->B->C->A。

例如，如果表中有以下資料：

以下是插入查詢：

insert into PCRELATIONS values(0, 'A', 'B', 'AB');
insert into PCRELATIONS values(0, 'B', 'C', 'BC');
insert into PCRELATIONS values(0, 'C', 'A', 'CA1');
insert into PCRELATIONS values(0, 'C', 'A', 'CA2');
insert into PCRELATIONS values(0, 'C', 'D', 'CD');

這個資料有兩個回圈，涉及 A->B->C->A，因為從 C 到 A 有兩條路徑。

我試圖得到這個結果：

leg1, leg2, leg3, p1id, p2id, p3id, val1, val2, val3
1, 2, 3, A, B, C, AB, BC, CA1,
1, 2, 4, A, B, C, AB, BC, CA2,

我在想下面的查詢會這樣做，但它回傳了太多的行。我哪里錯了？

select x.id as leg1, y.id as leg2, z.id as leg3, 
  x.pid as pid1, y.pid as pid2, z.pid as pid3, 
  x.value as val1, y.value as val2, z.value as val3  
from pcrelations x 
inner join pcrelations y on x.cid=y.pid 
inner join pcrelations z on y.cid=z.pid 
where z.cid=x.pid;

有什么想法嗎？

uj5u.com熱心網友回復：

您可以嘗試使用 CTE recursive 來找到您預期的 Rootpid然后執行條件聚合函式以獲得您的預期結果。

架構 (MySQL v8.0)

create table PCRELATIONS(
 id INT NOT NULL AUTO_INCREMENT, 
 pid char(1) NOT NULL, 
 cid char(1) NOT NULL, 
 value varchar(10),   
 PRIMARY KEY ( id ) 
);

insert into PCRELATIONS values(1, 'A', 'B', 'AB');
insert into PCRELATIONS values(2, 'B', 'C', 'BC');
insert into PCRELATIONS values(3, 'C', 'A', 'CA1');
insert into PCRELATIONS values(4, 'C', 'A', 'CA2');
insert into PCRELATIONS values(5, 'C', 'D', 'CD');

查詢#1

with recursive cte  as (
  SELECT id,pid,cid,value, 1 rn,id group_id
  FROM PCRELATIONS
  WHERE cid = 'A'
  union all
  select p.id,p.pid,p.cid,p.value, rn  1 ,group_id
  from       PCRELATIONS p
  inner join cte c
  on p.cid = c.pid AND p.cid <> 'A'
)
SELECT MAX(CASE WHEN rn = 3 THEN id END) leg1,
       MAX(CASE WHEN rn = 2 THEN id END) leg2,
       MAX(CASE WHEN rn = 1 THEN id END) leg3,
       MAX(CASE WHEN rn = 3 THEN pid END) pid1,
       MAX(CASE WHEN rn = 2 THEN pid END) pid2,
       MAX(CASE WHEN rn = 1 THEN pid END) pid3,
       MAX(CASE WHEN rn = 3 THEN value END) val1,
       MAX(CASE WHEN rn = 2 THEN value END) val2,
       MAX(CASE WHEN rn = 1 THEN value END) val3
FROM cte
GROUP BY group_id;

在 DB Fiddle 上查看

uj5u.com熱心網友回復：

您需要嚴格的 3 節點回圈，在這種情況下不需要遞回 CTE。

SELECT t1.id    leg1, t2.id    leg2, t3.id    leg3 
     , t1.pid   p1id, t2.pid   p2id, t3.pid   p3id
     , t1.value val1, t2.value val2, t3.value val3  
FROM pcrelations t1                       -- 1st level
JOIN pcrelations t2 ON t2.pid = t1.cid    -- join 2nd level
JOIN pcrelations t3 ON t3.pid = t2.cid    -- join 3rd level
WHERE t3.cid = t1.pid                     -- check for a loop
  AND t1.pid < t2.pid                     -- select only those loop copy which starts 
  AND t1.pid < t3.pid;                    -- from the node with the least pid value

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c58bdd205e0f61a71b8433cb10c72878

附注。不會回傳像A->A->A->A（正式匹配條件）之類的 3 節點回圈。如果您需要在這些回圈中使用<=WHERE 子句中的最后 2 個條件。

uj5u.com熱心網友回復：

只要確保連接表的值更大。

select 
  t1.id as leg1, t2.id as leg2, t3.id as leg3 
-- , t1.cid as cid1, t2.cid as cid2, t3.cid as cid3
, t1.pid as pid1, t2.pid as pid2, t3.pid as pid3
, t1.value as val1, t2.value as val2, t3.value as val3  
from pcrelations t1
inner join pcrelations t2
  on t2.pid = t1.cid 
 and t2.value > t1.value
inner join pcrelations t3 
  on t3.pid = t2.cid 
 and t3.cid = t1.pid 
 and t3.value > t2.value;

關于db<>fiddle 的演示在這里

標籤：mysql sql

上一篇：SQL搜索查詢多次顯示相同的結果，盡管它在我的資料庫中只有一次

下一篇：情緒得到反饋專案