Mysql

多級層次關係

  • January 17, 2016

我的桌子設計是

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

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

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