iT邦幫忙

0

Mysql 5.7 遞迴查詢

要怎麼用procedure取得所有下一層的會員 (不用特別排序)
像是:
call get_descendant_id(10)
=>5 19 3 9 17 21 1 2
call get_descendant_id(37)
=>36 45

圖片
https://www.evernote.com/l/Aqn_uvJXSj5H7q9C6Fv7e1R24hyH_311SxM/

CREATE TABLE `members` (
  `id` int(11) unsigned NOT NULL,
  `parent_id` int(11) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='會員';

insert into members (id,parent_id) values ('1','3'),('2','3'),('3','5'),('9','5'),('17','19'),('21','19'),('5','10'),('19','10'),('22','20'),('10','26'),('20','26'),('36','37'),('45','37'),('37','26'),('26',NULL);

+----+-----------+
| id | parent_id | parent_id是該id的上一層會員
+----+-----------+
| 1 | 3 |
| 2 | 3 |
| 3 | 5 |
| 9 | 5 |
| 17 | 19 |
| 21 | 19 |
| 5 | 10 |
| 19 | 10 |
| 22 | 20 |
| 10 | 26 |
| 20 | 26 |
| 36 | 37 |
| 45 | 37 |
| 37 | 26 |
| 26 | NULL |
+----+-----------+

2 個回答

2
一級屠豬士
iT邦大師 1 級 ‧ 2019-10-24 21:39:17
最佳解答
CREATE TABLE ithelp191024 (
  id int(11) unsigned NOT NULL
, parent_id int(11) unsigned DEFAULT NULL
);

insert into ithelp191024 (id,parent_id) values 
('1','3'),('2','3'),('3','5'),('9','5'),('17','19'),
('21','19'),('5','10'),('19','10'),('22','20'),
('10','26'),('20','26'),('36','37'),('45','37'),
('37','26'),('26',NULL);

--
delimiter $$
create procedure pid_subtree (pid int)
begin
    drop table if exists _pid_subtree;
    create table _pid_subtree (childid int unsigned not null,
        parentid int unsigned not null,
        primary key(childid, parentid));
    set @id = pid;
    insert into _pid_subtree
    select id, parent_id
      from ithelp191024 
     where parent_id = @id;
    repeat
        insert ignore into _pid_subtree
        select i.id, i.parent_id
          from ithelp191024 as i
          join _pid_subtree as s
            on i.parent_id = s.childid;
    until row_count() = 0 end repeat;
    select parent, group_concat(child) as childs
      from (select @id as parent
                 , parentid as child
              from _pid_subtree
             where parentid <> @id
             union 
            select @id
                 , childid
             from _pid_subtree
            ) a
      group by parent;
end;
$$
delimiter ;

call pid_subtree(10);

+--------+--------------------+
| parent | childs             |
+--------+--------------------+
|     10 | 2,9,17,21,3,5,19,1 |
+--------+--------------------+
1 row in set (0.07 sec)

call pid_subtree(37);

+--------+--------+
| parent | childs |
+--------+--------+
|     37 | 36,45  |
+--------+--------+
1 row in set (0.06 sec)
--
這道 procedure 會產生一個 table _pid_subtree
可以透過以下指令去得到如一般CTE常見的階層展示方式:

select parentid as parent
     , group_concat(childid) as childs
 from _pid_subtree
group by parentid;

+--------+--------+
| parent | childs |
+--------+--------+
|     37 | 36,45  |
+--------+--------+

因為剛才執行了求解 37,現在再求解 10的.
call pid_subtree(10);
再執行上面SQL Command

+--------+--------+
| parent | childs |
+--------+--------+
|      3 | 1,2    |
|      5 | 3,9    |
|     10 | 5,19   |
|     19 | 17,21  |
+--------+--------+
4 rows in set (0.00 sec)

--
加送你求上層節點的.

--
delimiter $$
CREATE PROCEDURE get_ancestors_id( pid int )
BEGIN
    drop temporary table if exists _ancestors;
    create temporary table _ancestors(parent int);
    set @id = pid;
    repeat
        select parent_id,count(*) into @parent,@y 
          from ithelp191024 
         where id=@id
         group by parent_id;
        if @y>0 then
            insert into _ancestors values(@parent);
            set @id=@parent;
        end if;
    until @parent is null or @y=0 end repeat;
    select * from _ancestors;
END;
$$
delimiter ;

call get_ancestors_id(2);

+--------+
| parent |
+--------+
|      3 |
|      5 |
|     10 |
|     26 |
|   NULL |
+--------+
5 rows in set (0.00 sec)

call get_ancestors_id(21);

+--------+
| parent |
+--------+
|     19 |
|     10 |
|     26 |
|   NULL |
+--------+

你的圖跟資料,是否在 5->19 這段不一致?

關於這類型的問題,MySQL到8版以後,有了CTE,會比較方便.
我另外有在此次鐵人賽,寫了關於 Postgresql 的 ltree.
對此類問題,有另外的方式.

https://ithelp.ithome.com.tw/articles/10227730

https://ithelp.ithome.com.tw/articles/10227989

有興趣的,可以參考.

看更多先前的回應...收起先前的回應...

我就在等您的解答
/images/emoticon/emoticon32.gif

qoo100116 iT邦新手 5 級 ‧ 2019-10-24 23:07:17 檢舉

感謝你~ 我知道CTE要在Mysql 8才有
所以想這個遞迴查詢,想了兩天還是想不出來
我也有查到類似的應用
https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159
但你的做法比較易懂~

甲土豆 iT邦新手 5 級 ‧ 2019-10-25 09:34:06 檢舉

會不會,太專業了,覺得厲害/images/emoticon/emoticon32.gif

另外做一份補充資料.

https://ithelp.ithome.com.tw/articles/10228784

0
firecold
iT邦新手 3 級 ‧ 2019-10-24 18:19:42

你的需求我感覺用nest tree 會比較符合

基本概念
http://asika.windspeaker.co/post/3488-nested-set-model

如果你是用laravel 可以參考
https://github.com/lazychaser/laravel-nestedset

單純sql的話

$sql = "select id form `members` where parent_id = '$parent_id'";

阿阿打太快沒注意到要用procedure...
當就沒看到瞜 sor

我要發表回答

立即登入回答