Starrocks和MySQL中將一列轉換為多行。

有一個團隊信息表ods_mes_team，里面有一個teamMember字段，存放的是該團隊中每個成員的ID。

我現在想知道該團隊每個成員的姓名，需要將每個成員ID提取出來。

WITH numbers AS (
    SELECT 1 AS n UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10  -- 最后一個數字
),
ods_mes_team AS (
    SELECT  1 id, '10001,20012,50076,9000'  teamMember
    UNION ALl  
    SELECT  2 , '20004,10088,10049'  
)
SELECT 
    t.id AS original_id,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.teamMember, ',', num.n), ',', -1)) AS member_id
FROM 
    ods_mes_team t
JOIN 
    numbers num
ON 
    num.n <= LENGTH(t.teamMember) - LENGTH(REPLACE(t.teamMember, ',', '')) + 1;

執行結果：

然后關聯用戶信息表，就可以拿到每個員工的姓名

WITH numbers AS (
    SELECT 1 AS n UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10  -- 最后一個數字
),
ods_mes_team AS (
    SELECT  1 id, '10001,20012,50076,90000'  teamMember
    UNION ALl  
    SELECT  2 , '20004,10088,10049'  
),
user_info AS (
    SELECT  '10001' user_id,  '王偉'  user_name UNION ALL 
    SELECT  '10002', '楊莉' UNION ALL
    SELECT  '10049', '郝振杰' UNION ALL
    SELECT  '10088', '姚佳' UNION ALL
    SELECT  '20004', '鄧潔' UNION ALL
    SELECT  '20012', '趙媛' UNION ALL
    SELECT  '90000', '葛文會' UNION ALL
    SELECT  '50076', 'Andy' UNION ALL
    SELECT  '00002', '楊碩菁' UNION ALL
    SELECT  '00002', '王彬' 
),
member_info AS (
SELECT 
    t.id AS original_id,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.teamMember, ',', num.n), ',', -1)) AS member_id
FROM 
    ods_mes_team t
JOIN 
    numbers num
ON 
    num.n <= LENGTH(t.teamMember) - LENGTH(REPLACE(t.teamMember, ',', '')) + 1
)
SELECT 
m.original_id,
m.member_id,
u.user_name 
FROM    member_info m
LEFT JOIN  user_info u  ON  u.user_id = m.member_id ;
執行結果：
 
 StarRocks中如何實現這樣的列轉行？

WITH ods_mes_team AS (
    SELECT  1 teamNo, '10001,20012,50076,90000'  teamMember
    UNION ALl  
    SELECT  2 , '20004,10088,10049'      
    UNION ALl  
    SELECT  3 , '10004,40001,30200'  
)
SELECT 
    t.teamNo,
    t2.user_id
FROM 
    (SELECT teamNo , teamMember FROM  ods_mes_team) t, 
    unnest(split(t.teamMember, ',')) AS t2(user_id) ;
執行結果：
 

posted @ 2025-06-19 15:51 業余磚家閱讀(98) 評論(0) 收藏舉報

刷新頁面返回頂部

業余磚家

沒有比人更高的山，沒有比腳更長的路。

Starrocks和MySQL中將一列轉換為多行。

公告