有没有办法将一列的多个值加载到新行中到sql中的单行中

Question

这就是我的数据的样子，

emp_id,skills

1234,python|java|sql|R|javascript

5639,C|HTML|php|perl

这就是数据需要加载到表中的方式

emp_id  skills

        

       python

1234    java

        sql

        R

        perl

        C

5639    HTML

        php

其实我已经替换了| 与 \n 但它没有被加载到下一行，而是只是通过添加空格来加载。我将使用 python etl 将数据加载到表中，因此即使是后处理我也可以添加。有什么建议么？？

Answer 1

这是关系数据库的行为，不能仅emp_id每个组显示一次并且其所有元素每行显示一个。改变数据显示方式是前端的特权和责任，而不是数据库的。因此，请在 Python 中执行此操作。话虽如此，Impala 具有SPLIT_PART()字符串函数，它返回由作为参数传递的分隔符分隔的字符串的第 n 个标记。因此，与一系列连续整数交叉连接，然后应用SPLIT_PART(skills,'|',i)即可满足您的需要。实际上，以我（从来没有）谦虚的观点来看，每当有人向您抛出格式如此不合适的文件以将其加载到数据库中时，您就应该这样做。始终使用下面的技术垂直化逗号/条/分号/或任何分隔的“值”列表，并垂直存储数据。：WITH-- your inputinput( emp_id,skills) AS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT 1234,'python|java|sql|R|javascript'UNION ALL SELECT 5639,'C|HTML|php|perl'),-- a big enough series of integers ..i(i) AS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT 1UNION ALL SELECT 2UNION ALL SELECT 3UNION ALL SELECT 4UNION ALL SELECT 5UNION ALL SELECT 6UNION ALL SELECT 7UNION ALL SELECT 8)SELECT&nbsp; emp_id, SPLIT_PART(skills,'|',i) AS skillFROM inputCROSS JOIN iWHERE SPLIT_PART(skills,'|',i) <> ''ORDER BY&nbsp;&nbsp; emp_id, i;-- out&nbsp; emp_id |&nbsp; &nbsp;skill&nbsp; &nbsp;&nbsp;-- out --------+-------------- out&nbsp; &nbsp; 1234 | python-- out&nbsp; &nbsp; 1234 | java-- out&nbsp; &nbsp; 1234 | sql-- out&nbsp; &nbsp; 1234 | R-- out&nbsp; &nbsp; 1234 | javascript-- out&nbsp; &nbsp; 5639 | C-- out&nbsp; &nbsp; 5639 | HTML-- out&nbsp; &nbsp; 5639 | php-- out&nbsp; &nbsp; 5639 | perl使用两个横杠/逗号分隔的列，它可能如下所示：WITH-- your input, enhancedinput( emp_id,skills,pubs) AS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT 1234,'python|java|sql|R|javascript','ship inn,anchor,stag'UNION ALL SELECT 5639,'C|HTML|php|perl'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,'black horse,crown,mitre'),-- a big enough series of integers ..i(i) AS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT 1UNION ALL SELECT 2UNION ALL SELECT 3UNION ALL SELECT 4UNION ALL SELECT 5UNION ALL SELECT 6UNION ALL SELECT 7UNION ALL SELECT 8),-- another big enough series of integers ..j(j) AS (&nbsp; SELECT&nbsp; &nbsp; i AS j&nbsp; FROM i)SELECT&nbsp; emp_id, i AS skill_sequence, SPLIT_PART(skills,'|',i) AS skill, j AS pub_sequence, SPLIT_PART(pubs,',',j) AS pubFROM inputCROSS JOIN iCROSS JOIN jWHERE SPLIT_PART(skills,'|',i) <> ''&nbsp; AND SPLIT_PART(pubs,&nbsp; ',',j) <> ''ORDER BY&nbsp;&nbsp; emp_id, i, j;-- out&nbsp; emp_id | skill_sequence |&nbsp; &nbsp;skill&nbsp; &nbsp; | pub_sequence |&nbsp; &nbsp; &nbsp;pub&nbsp; &nbsp; &nbsp;-- out --------+----------------+------------+--------------+--------------- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | python&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | ship inn-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | python&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | anchor-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | python&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | stag-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | java&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | ship inn-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | java&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | anchor-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | java&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | stag-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | sql&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | ship inn-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | sql&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | anchor-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | sql&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | stag-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 | R&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | ship inn-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 | R&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | anchor-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 | R&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | stag-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 | javascript |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | ship inn-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 | javascript |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | anchor-- out&nbsp; &nbsp; 1234 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 | javascript |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | stag-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | black horse-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | crown-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | mitre-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | HTML&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | black horse-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | HTML&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | crown-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | HTML&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | mitre-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | php&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | black horse-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | php&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | crown-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | php&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | mitre-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 | perl&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 | black horse-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 | perl&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | crown-- out&nbsp; &nbsp; 5639 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 | perl&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 | mitre