mysql - 逗号分隔值成行在MySQL中

SELECT DISTINCT jp.skills
FROM job_profile jp
UNION
SELECT js.skills
FROM job_seeker_profile js

结果：

|skills              |
|php                 |
|PHP,Jquery,MVC      |
|java                |
|.net                |
|Tally               |
|php, mysql, yii     |
|css, html, bootstrap|
|javascript, json    |

但我需要将它作为行的每个项目（每个逗号分隔的值作为行）

预期结果：

|skills   |
|yii      |
|PHP      |
|Jquery   |
|MVC      |
|.net     |
|Tally    |
|bootstrap|
|css      |
|html     |

最佳答案

创建一个拆分函数，该函数将在上拆分数据，然后创建按行数据

SELECT distinct  Split.fn.value('.', 'VARCHAR(100)') AS skills
 FROM
 (SELECT  CAST ('<a>' + REPLACE(skills, ',', '</a><a>') + '</a>' AS XML) AS Data
  FROM  job_profile
 ) AS A CROSS APPLY Data.nodes ('/a') AS Split(fn);

注意：根据需要更新内部查询

更新：在您的情况下查询将

SELECT distinct  Split.fn.value('.', 'VARCHAR(100)') AS skills
 FROM
 (SELECT  CAST ('<a>' + REPLACE(jp.skills, ',', '</a><a>') + '</a>' AS XML) AS Data
  FROM  job_profile jp
  UNION
    SELECT js.skills
    FROM job_seeker_profile js
 ) AS A CROSS APPLY Data.nodes ('/a') AS Split(fn);

关于mysql - 逗号分隔值成行在MySQL中，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37585732/