層次結構查詢Mysql

我有一張這樣的桌子…

==========================================================================
UID    ||      PARENT ID           ||  SPONSOR ID
==========================================================================
1      ||         0                ||     0
2      ||         1                ||     1
3      ||         1                ||     1
4      ||         2                ||     1
5      ||         2                ||     2
6      ||         4                ||     1
7      ||         0                ||     0
8      ||         7                ||     7

假設我是 UID = 1 的使用者，現在我想查詢以便可以獲取網路中的所有節點，如何查詢此表以便獲得以下結果：

============================================================================
UID     ||        PARENT ID          || SPONSOR
============================================================================
2       ||          1                || 1
3       ||          1                || 1
4       ||          2                || 1
5       ||          2                || 2
6       ||          4                || 1       

我嘗試使用此查詢

select @pv:=uid as uid, parentid,sponsorid from genealogy 
join
(select @pv:=1)tmp
where parentid=@pv

但結果只是

============================================================================
UID     ||        PARENT ID          || SPONSOR
============================================================================
2       ||          1                || 1
4       ||          2                || 1
6       ||          4                || 1

我的查詢有什麼問題..有人可以幫助我嗎？非常感謝

如果樹的深度限制為 3（您的範例）。你可以這樣做。

(SELECT t1.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1) 
UNION
(SELECT t2.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1)
UNION
(SELECT t3.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1);

我的解決方案不尊重 DRY 原則，但這是我目前找到的唯一簡單的解決方案。

如果您事先不知道樹的最大深度，請查看以下討論該主題的問題和連結：

• https://stackoverflow.com/questions/5688376/mysql-select-tree-parent-ids
• https://stackoverflow.com/questions/18083546/mysql-recursivetree-parent-child-category
• http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

希望這有幫助！

在 Mysql 8+ 中實現遞歸 CTE 之前，層次結構基於一些奇怪的概念，例如“嵌套集”。在早期版本的 MySQL 中沒有辦法進行遞歸查詢。當然，您總是能夠使用複合語句創建一個儲存常式，以實現您想要的任何類型的遞歸。

引用自：https://dba.stackexchange.com/questions/86951