Mysql让爷爷孙子一路下来

Question

+-----------+-----------------+
| AccountID | ParentAccountID |
+-----------+-----------------+
|         1 |            NULL |
|         2 |               1 |
|         3 |               2 |
|         5 |               3 |
|         6 |               5 |
|         7 |               6 |
|         8 |               7 |
|         9 |               8 |
|        10 |               9 |
|        11 |              10 |
|        12 |              11 |
|        13 |              12 |
|        14 |              13 |
|        15 |              14 |
|        16 |              15 |
|        17 |              16 |
|        18 |              17 |
|        19 |              18 |
|        20 |              19 |
+-----------+-----------------+

我正在寻找一个简单的查询来根据父帐户 ID 列出所有子项和孙项。

例如 AccountID (1) 的子代和孙代为 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 ,20AccountID(14) 的子代和孙代为 15,16,17,18,19,20

我尝试了很多查询，只返回上一级。请帮忙解决这个问题。提前致谢

Answer 1

您想要整个层次结构，而不仅仅是两个级别。因此，为此您必须在表中添加另一列。

+-----------+-----------------++-----------------+
| AccountID | ParentAccountID | Hierarchy        |
+-----------+-----------------+------------------+
|         1 |            NULL |                  | 
|         2 |               1 |-1->              |
|         3 |               2 |-1->-2->          |
|         5 |               3 |-1->-2->-3->      |
|         6 |               5 |-1->-2->-3->-5->  |
+-----------+-----------------+-+----------------+ etc.

我只是给出如何构建结构的提示。

DELIMITER $$

CREATE PROCEDURE build_hierarchy ()
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
 Declare var_AccountId integer;
 Declare var_ParentAccountID integer;
 Declare var_Hierarchy integer;
 Declare cur_all_accounts cursor for select * from accounts;
-- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;
get_data: LOOP

 FETCH cur_all_accounts INTO var_AccountId, var_ParentAccountID,var_Hierarchy ;

 IF v_finished = 1 THEN 
 LEAVE get_data;
 END IF;

Update Accounts set hierarchy = (select Hierarchy + '-' + accountId + '->' from Accounts 
where accountId = var_ParentaccountId) where AccountID = var_accountId;

 END LOOP get_data;

 CLOSE cur_all_accounts;

END$$

DELIMITER ;

然后运行该过程。

CALL build_hierarchy();

要获取 ParentAccountId = 1 的层次结构，请运行以下查询。

select AccountID from Accounts where Hierarchy like = '%-' + parentAccountId+'->%';