各位大神,小弟我有一個情境是關於Hierarchical Query的問題
Table: hierarchy
DB:Mysql
Version:8.0.25
目前資料表內容如下:
我想要透過語法找到所有下線,使用了以下SQL語句
select * from(
select h2.user_id, h3.user_id from hierarchy as h1
left join hierarchy as h2 on h1.user_id = h2.parentId
left join hierarchy as h3 on h2.user_id = h3.parentId
where h1.user_id = "2");
結果會變成如下:
預期結果如下:
我希望將輸出結果攤平
懇請各位大大們協助
不要拿SQL語法當程式用。
當然,要處理並不是不行。
GROUP_CONCAT、UNION、這幾個關鍵字給你參考。
create table ithelp210604 (
id int not null auto_increment primary key
, user_id int not null
, parent_id int
);
insert into ithelp210604(user_id, parent_id) values
(1, null), (2, 1), (3, 1), (4, 2), (5, 2),
(6, 3), (7, 3), (8, 4), (9, 4);
select *
from ithelp210604;
+----+---------+-----------+
| id | user_id | parent_id |
+----+---------+-----------+
| 1 | 1 | NULL |
| 2 | 2 | 1 |
| 3 | 3 | 1 |
| 4 | 4 | 2 |
| 5 | 5 | 2 |
| 6 | 6 | 3 |
| 7 | 7 | 3 |
| 8 | 8 | 4 |
| 9 | 9 | 4 |
+----+---------+-----------+
9 rows in set (0.00 sec)
with recursive t1 as (
select user_id
, parent_id
, 1 as lvl
, cast(user_id as char(20)) as path
from ithelp210604
where user_id = 2
union all
select b.user_id
, b.parent_id
, t1.lvl + 1
, concat(t1.path, ' -> ', cast(b.user_id as char(2)))
from t1
join ithelp210604 b
on t1.user_id = b.parent_id
)
select *
from t1;
+---------+-----------+------+-------------+
| user_id | parent_id | lvl | path |
+---------+-----------+------+-------------+
| 2 | 1 | 1 | 2 |
| 4 | 2 | 2 | 2 -> 4 |
| 5 | 2 | 2 | 2 -> 5 |
| 8 | 4 | 3 | 2 -> 4 -> 8 |
| 9 | 4 | 3 | 2 -> 4 -> 9 |
+---------+-----------+------+-------------+
5 rows in set (0.01 sec)
若不需要 user_id = 2 , 可以在最後的查詢加上 where 過濾
where user_id = 2
或是 where lvl > 1