数据库中有业务表t1和字典表dictionary
t1表: dictionary表:
SELECT col,a[1] a1,a[2] a2,a[3] a3效果:
FROM
(
SELECT regexp_split_to_array( col, ',' ), col FROM t1
) AS dt (a)
SELECT效果:
col,
SPLIT_PART( col, ',', 1 ) a1,
SPLIT_PART( col, ',', 2 ) a2,
SPLIT_PART( col, ',', 3 ) a3
FROM
t1
ALTER TABLE t1 ADD COLUMN col1 VARCHAR(30);2、再用 SPLIT_PART 函数填充新的列
ALTER TABLE t1 ADD COLUMN col2 VARCHAR(30);
ALTER TABLE t1 ADD COLUMN col3 VARCHAR(30);
UPDATE t1效果:
SET col1 = SPLIT_PART( col, ',', 1 ),
col2 = SPLIT_PART( col, ',', 2 ),
col3 = SPLIT_PART( col, ',', 3 );
SELECT col,效果:
(SELECT name from dictionary WHERE id=a[1]) a1,
(SELECT name from dictionary WHERE id=a[2]) a2,
(SELECT name from dictionary WHERE id=a[3]) a3
FROM
(
SELECT regexp_split_to_array( col, ',' ), col FROM t1
) AS dt (a)
SELECT col,CONCAT_WS(',',a1,a2,a3) as names效果:
FROM
(
SELECT col,
(SELECT name from dictionary WHERE id=a[1]) a1,
(SELECT name from dictionary WHERE id=a[2]) a2,
(SELECT name from dictionary WHERE id=a[3]) a3
FROM
(
SELECT regexp_split_to_array( col, ',' ), col FROM t1
) AS dt (a)
) as temp
SELECT max(array_length(regexp_split_to_array(col,','),1))
FROM t1;
免责申明:
本文系转载,版权归原作者所有,如若侵权请联系我们进行删除!
《数据治理行业实践白皮书》下载地址:https://fs80.cn/4w2atu
《数栈V6.0产品白皮书》下载地址:https://fs80.cn/cw0iw1
想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友,浏览袋鼠云官网:https://www.dtstack.com/?src=bbs
同时,欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」,交流最新开源技术信息,群号码:30537511,项目地址:https://github.com/DTStack