如何将数据从一列分割到另一表中的三列?SQL[英] How to split data from one column to three columns in another table? SQL

问题描述

我有两列 id_student 和 hobbies 的表 (inf),如下所示:

ID_student =  1      
Hobbies =   "music, cooking, bassguitar" 

我想通过将爱好拆分到包含以下列的三列中的另一个表(爱好)来复制爱好:

ID_student   hobby1   hobby2     hobby3
1            music    cooking    bassguitar

我怎么能在 Postgres 中写出类似的东西?

推荐答案

有很多方法可以做到这一点.一种方法是使用 string_to_array 函数:

INSERT INTO hobbies (id, hobby1, hobby2, hobby3) 
SELECT id,hobbies_array[1],hobbies_array[2],hobbies_array[3] FROM 
  (
    SELECT id,string_to_array(hobbies,',') AS hobbies_array 
    FROM inf
  ) AS foo;

本文地址:https://www.itbaoku.cn/post/1739745.html