mysql 将含有逗号的string 字符串分割（行转列）

雨后d夏天

已于 2024-10-25 16:21:41 修改

阅读量540

点赞数 1

文章标签： mysql 数据库

于 2024-10-25 16:13:38 首次发布

本文链接：https://blog.youkuaiyun.com/sj_liu/article/details/143236787

版权

直接上代码：

分割（行转列）并去重

SELECT distinct  id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(contract_email, ',', n), ',', -1) AS item
FROM `product`.supplier_service_metadata ,
     (SELECT @rownum := @rownum + 1 AS n
      FROM (SELECT @rownum := 0) r,
           `product`.supplier_service_metadata) x
WHERE n <= (LENGTH(contract_email) - LENGTH(REPLACE(contract_email, ',', '')) + 1)

再根据id不同重新整合到一条数据

select id, GROUP_CONCAT(item) as emails from (
SELECT distinct  id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(contract_email, ',', n), ',', -1) AS item
FROM `product`.supplier_service_metadata ,
     (SELECT @rownum := @rownum + 1 AS n
      FROM (SELECT @rownum := 0) r,
           `product`.supplier_service_metadata) x
WHERE n <= (LENGTH(contract_email) - LENGTH(REPLACE(contract_email, ',', '')) + 1)
)A
group by id