多級層次關係

我的桌子設計是

users (id, username..., parent_id)

數據是

id    username      parent_id
-------------------------------
1  |  admin       | null
2  |  reseller 1  | 1
3  |  client 1    | 1
4  |  reseller 2  | 1
5  |  reseller 3  | 2
6  |  reseller 4  | 2
7  |  client 2    | 5
8  |  client 3    | 6

我想得到所有的後代id 1

我研究了鄰接列表、嵌套列表和閉包表設計，但得出的結論是，基於會話的鄰接列表在我的情況下可能會更好。

我在https://stackoverflow.com/questions/8104187/hierarchical-queries-in-mysql找到了一個範例

SELECT  @id :=
       (
       SELECT  senderid
       FROM    mytable
       WHERE   receiverid = @id
       ) AS person
FROM    (
       SELECT  @id := 5
       ) vars
STRAIGHT_JOIN
       mytable
WHERE   @id IS NOT NULL

我試圖修改這個查詢以獲得這樣的結果

SELECT  group_concat(@id :=
       (
       SELECT  id
       FROM    users
       WHERE   parent_id = @id
       )) AS u
FROM    (
       SELECT  @id := 1
       ) vars
STRAIGHT_JOIN
       users
WHERE   @id IS NOT NULL

但它不起作用。這是SQLFiddle

在使查詢工作時需要幫助。

最後，我按照@RolandoDBA 在Find最高級別的分層欄位中的建議創建了一個函式：with vs without CTEs

DELIMITER $$

DROP FUNCTION IF EXISTS `siblings` $$
CREATE FUNCTION `siblings` (GivenID INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

   DECLARE rv,q,queue,queue_children VARCHAR(1024);
   DECLARE queue_length,front_id,pos INT;

   SET rv = '';
   SET queue = GivenID;
   SET queue_length = 1;

   WHILE queue_length > 0 DO
       SET front_id = FORMAT(queue,0);
       IF queue_length = 1 THEN
           SET queue = '';
       ELSE
           SET pos = LOCATE(',',queue) + 1;
           SET q = SUBSTR(queue,pos);
           SET queue = q;
       END IF;
       SET queue_length = queue_length - 1;

       SELECT IFNULL(qc,'') INTO queue_children
       FROM (SELECT GROUP_CONCAT(id) qc
       FROM `users` WHERE parent_id = front_id) A;

       IF LENGTH(queue_children) = 0 THEN
           IF LENGTH(queue) = 0 THEN
               SET queue_length = 0;
           END IF;
       ELSE
           IF LENGTH(rv) = 0 THEN
               SET rv = queue_children;
           ELSE
               SET rv = CONCAT(rv,',',queue_children);
           END IF;
           IF LENGTH(queue) = 0 THEN
               SET queue = queue_children;
           ELSE
               SET queue = CONCAT(queue,',',queue_children);
           END IF;
           SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
       END IF;
   END WHILE;

   RETURN rv;

END $$

然後為了獲取父母的兄弟姐妹/孩子，使用上面創建的函式進行簡單呼叫siblings就足夠了。

SELECT siblings(id) AS `siblings` from `users` where `id` = 1

這將結果返回為

siblings
---------------
2,3,4,5,6,7,8

連結答案中的查詢是“hack”，因為那裡的 OP 在評論中清楚地描述了並且可以隨時停止使用 MySQL 升級，因為沒有記錄會話變數的確切行為。

除此之外，還有關於找到所有祖先，而不是所有後代的問題。在鄰接列表中，一個節點最多有一個父節點，但可能有多個子節點。所以這個查詢/黑客在你的情況下不起作用。

鄰接表模型是 SQL 中所有層次模型中最簡單的。其他 3 個（嵌套集、評估路徑、閉包表）更複雜，因為 - 在某種程度上 - 儲存了冗餘資訊。作為交換，一些查詢更容易編寫。在其他 3 個模型中的任何一個中，解決方案都非常簡單，不需要遞歸查詢。

因此，您似乎有兩種選擇：

• 評估您使用鄰接列表模型的決定並使用不同的模型（或模型的組合）
• 編寫一個解決問題的過程/函式。@RolandoDBA 在這個網站上有一個舊答案，它提供了幾個解決這個（和相關）問題的過程。我不確定它們的效率如何，但它們肯定會起作用：

查找分層欄位的最高級別：使用 vs 不使用 CTE。

您將在以下連結中找到更多有用的資訊：

• MySQL 中的樹和其他層次結構，在使用 MySQL 5 和 6 完成它，第 20 章。版權所有 © Peter Brawley 和 Arthur Fuller 2011。
• EXPLAIN EXTENDED： MySQL 中的分層查詢，由 Quassnoi 撰寫（是的，它與發布您嘗試的查詢相同。）在他的部落格中，他有幾個分層查詢可以找到祖先、後代等。
• 用於 MySQL 的分層數據模型，作者：Bill Karwin。一個幻燈片，描述了 MYSQL 中各種層次模型的優缺點。略微偏向於關閉表。

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