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) ;

执行结果：