SQL 語法不知道怎下?

hierarchical query mysql database

t628x7600 2021-06-04 08:02:57 ‧ 1827 瀏覽

分享至

各位大神,小弟我有一個情境是關於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");

結果會變成如下:

預期結果如下:

我希望將輸出結果攤平

懇請各位大大們協助

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

㊣浩瀚星空㊣

iT邦大神 1 級 ‧ 2021-06-04 08:18:42

最佳解答

不要拿SQL語法當程式用。
當然，要處理並不是不行。
GROUP_CONCAT、UNION、這幾個關鍵字給你參考。

回應 1
分享
檢舉

t628x7600 iT邦新手 5 級 ‧ 2021-06-04 08:28:05 檢舉

大大的意思是說盡量query到server 用程式語言去處理data structure嗎?

登入發表回應

一級屠豬士

iT邦大師 1 級 ‧ 2021-06-04 08:37:26

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