我有一個存盤這樣的關系的表：
ParentName ParentID ChildName ChildId
-------------------------------------
Name1 Guid NameA Guid
Name2 Guid NameB Guid
Name3 Guid NameC Guid
NameA Guid NameY Guid
NameB Guid NameX Guid
NameC Guid NameZ Guid
NameY Guid Name1A Guid
我的目標是檢索堆疊的資料，無論層次結構有多遠，子列都會顯示最頂層的父級是誰。
我知道這是一個遞回 CTE，但我不知道如何在不破壞最大遞回次數的情況下將查詢中的遞回聯接寫回錨點。父查詢使用WHERE子句有 78 條記錄，但總級別不應超過 4 或 5 個。父查詢可以有多個子節點。
uj5u.com熱心網友回復：
下面的查詢應該可以作業。雖然它受到資料庫允許的最大遞回的影響，在 SQL Server 的情況下默認為 100。所以它應該作業到嵌套父子關系的多層級。
WITH RECURSIVE cte AS (
SELECT ChildID,ChildName,ParentID,ParentName, 1 AS Level FROM parents
UNION ALL
SELECT cte.ChildID,
cte.ChildName,
GrandParent.ParentID,
GrandParent.ParentName,
Level 1
FROM cte
INNER JOIN parents AS GrandParent
ON cte.ParentID=GrandParent.ChildID
)
SELECT ChildID,ChildName,ParentID,ParentName
FROM(
SELECT cte.*,
ROW_NUMBER() OVER(PARTITION BY ChildID,ChildName ORDER BY Level DESC) AS rnk
FROM cte
) Rank_Highest_Level
WHERE rnk=1
ORDER BY ChildID;
這是 DB Fiddle 中的一個作業示例。
uj5u.com熱心網友回復：
這是此遞回查詢的一個作業示例。
CREATE TABLE parents (
ParentName char(10),
ParentID char(10),
ChildName char(10),
ChildId char(10) );
insert into parents values( 'a',1,'b',2);
insert into parents values( 'b',2,'c',3);
insert into parents values( 'c',3,'d',4);
insert into parents values( 'd',4,'e',5);
insert into parents values( 'e',5,'f',6);
insert into parents values( 'f',6,'g',7);
insert into parents values( 'g',7,'h',8);
select
p1.pname, p1.pid,
p2.pname, p2.pid,
p3.pname, p3.pid,
p4.pname, p4.pid,
p5.pname, p5.pid,
p5.cname, p5.cid
from
(select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p1 right join
(select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p2 on p1.cid=p2.pid right join
(select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p3 on p2.cid=p3.pid right join
(select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p4 on p3.cid=p4.pid right join
(select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p5 on p4.cid=p5.pid
order by
p1.pid,p2.pid,p3.pid,p4.pid,p5.pid;
DROP TABLE parents;
``
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/425360.html