SQL 将一列拆分成多列

亦凉 2024-03-17 12:29 51阅读 0赞

数据库中有业务表t1和字典表dictionary

t1表： dictionary表：

![65a01400ce2344d2a7c9625bd8682fae.png][] ![7a79f83f43444d4eb197b48587aa096e.png][]

#### 需求一：将col列拆分成三列 ####

SQL 代码如下所示：

方法一：

SELECT col,a[1] a1,a[2] a2,a[3] a3
    FROM
    	( 
    		SELECT regexp_split_to_array( col, ',' ), col FROM t1 
    	) AS dt (a)

效果：

![ee44c99514c94af6a2e5bc0fcc74e861.png][]

方法二：

SELECT
    	col,
    	SPLIT_PART( col, ',', 1 ) a1,
    	SPLIT_PART( col, ',', 2 ) a2,
    	SPLIT_PART( col, ',', 3 ) a3 
    FROM
    	t1

效果：

![bc4b279f6a1a41748e06e2bd748b3333.png][]

##### 假设想把 "col" 列分成 "col1" 、 "col2"、 "col3"，SQL语句： #####

###### 1、先添加新的列 ######

ALTER TABLE t1 ADD COLUMN col1 VARCHAR(30);
    ALTER TABLE t1 ADD COLUMN col2 VARCHAR(30);
    ALTER TABLE t1 ADD COLUMN col3 VARCHAR(30);

###### 2、再用 `SPLIT_PART` 函数填充新的列 ######

UPDATE t1 
    SET col1 = SPLIT_PART( col, ',', 1 ),
    col2 = SPLIT_PART( col, ',', 2 ),
    col3 = SPLIT_PART( col, ',', 3 );

效果：

![05de748392cf4d7e9d21ade72f8da09f.png][]

#### 需求二：列col保存的数据，是字典表dictionary中id值拼接的字符串，想关联出对应 的name值。 ####

SQL 代码如下所示：

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)

效果：

![9eb58346c3ad46f7b595f09a892790df.png][]

#### 需求三：列col转化为字典表dictionary中name拼接的字符串 ####

SQL 代码如下所示：

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

效果：

![b1f92b8b582940b0a3b9d2f43607db2c.png][]

#### 总结 ： ####

我们可能会遇到按分隔符拆成多行或者多列的情况，以及复制的业务需求，只需视情况调整sql语句，或者编写存储过程。

对于列col的值，按分隔符拆分为多列数据时，不知道需要拆分成几列，可以先用以下sql查询出列数。

SELECT max(array_length(regexp_split_to_array(col,','),1))
    FROM t1;

[65a01400ce2344d2a7c9625bd8682fae.png]: https://img-blog.csdnimg.cn/65a01400ce2344d2a7c9625bd8682fae.png
[7a79f83f43444d4eb197b48587aa096e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/7d3d9156dfae41a7ba8977d2f0d3cd21.png
[ee44c99514c94af6a2e5bc0fcc74e861.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/fc70b79aa9bc467eb3a234bf5db267a8.png
[bc4b279f6a1a41748e06e2bd748b3333.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/fca7425d64aa487b994075239562fd50.png
[05de748392cf4d7e9d21ade72f8da09f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/023750a30bae4b8f8f96ce0312a21e57.png
[9eb58346c3ad46f7b595f09a892790df.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/9623a4406ac94bec8a093f13d4cde6b7.png
[b1f92b8b582940b0a3b9d2f43607db2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/105cf9328bf149f0854f9254dbdf9eb8.png