逗号将值分隔为mysql中的行
逗号将值分隔为mysql中的行
提问于 2016-06-02 07:50:01
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 |回答 1
Stack Overflow用户
发布于 2016-06-02 08:04:23
创建一个拆分函数，将数据拆分，然后创建逐行数据。
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); 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37585732
复制相关文章
相似问题
腾讯云开发者
