如何使用SQL将表中分号分隔的同列字段拆分为同列多行
拆分分号分隔产品字段的SQL实现方案
该需求核心为分隔符拼接字段的行转多行(拆分多值),再配合窗口函数实现同公司仅首行保留公司名的效果,所有主流关系型数据库均支持以下实现方案。
核心逻辑
实现分为两步:
- 第一步:将
Products字段中按指定分隔符拆分内容为独立的单产品行 - 第二步:用窗口函数给同一
Company下的拆分结果排序,仅首行保留Company值,其余行置空
假设原始表名为company_product,表结构与示例一致。
分数据库可运行代码
MySQL 8.0+ 实现
WITH split_products AS ( -- 递归CTE拆分分号分隔的产品 SELECT Company, TRIM(SUBSTRING_INDEX(Products, '; ', 1)) AS product, TRIM(SUBSTRING(Products, LENGTH(SUBSTRING_INDEX(Products, '; ', 1)) + 2)) AS remaining_products FROM company_product UNION ALL SELECT Company, TRIM(SUBSTRING_INDEX(remaining_products, '; ', 1)) AS product, TRIM(SUBSTRING(remaining_products, LENGTH(SUBSTRING_INDEX(remaining_products, '; ', 1)) + 2)) FROM split_products WHERE remaining_products != '' ), ranked AS ( -- 给同一公司的产品分组排序 SELECT Company, product, ROW_NUMBER() OVER (PARTITION BY Company ORDER BY (SELECT 0)) AS rn FROM split_products ) -- 仅首行保留公司名,其余置空 SELECT CASE WHEN rn = 1 THEN Company ELSE NULL END AS Company, product AS Products FROM ranked;
PostgreSQL 实现
WITH ranked AS ( SELECT Company, UNNEST(STRING_TO_ARRAY(Products, '; ')) AS product, -- 拆分同时返回序号保证顺序 ROW_NUMBER() OVER (PARTITION BY Company ORDER BY ordinality) AS rn FROM company_product, LATERAL UNNEST(STRING_TO_ARRAY(Products, '; ')) WITH ORDINALITY AS t(product, ordinality) ) SELECT CASE WHEN rn = 1 THEN Company ELSE NULL END AS Company, product AS Products FROM ranked;
SQL Server 2016+ 实现
WITH split_products AS ( SELECT Company, TRIM(value) AS product FROM company_product -- 自带函数仅支持单分隔符,需额外处理空格 CROSS APPLY STRING_SPLIT(Products, ';') ), ranked AS ( SELECT Company, product, ROW_NUMBER() OVER (PARTITION BY Company ORDER BY (SELECT 0)) AS rn FROM split_products WHERE product != '' -- 过滤空值 ) SELECT CASE WHEN rn = 1 THEN Company ELSE NULL END AS Company, product AS Products FROM ranked;
Oracle 11g+ 实现
WITH split_products AS ( SELECT Company, REGEXP_SUBSTR(Products, '[^;]+', 1, LEVEL) AS product FROM company_product CONNECT BY LEVEL <= REGEXP_COUNT(Products, ';') + 1 AND PRIOR Company = Company AND PRIOR SYS_GUID() IS NOT NULL -- 避免跨行递归重复 ), ranked AS ( SELECT Company, TRIM(product) AS product, ROW_NUMBER() OVER (PARTITION BY Company ORDER BY LEVEL) AS rn FROM split_products WHERE product IS NOT NULL ) SELECT CASE WHEN rn = 1 THEN Company ELSE NULL END AS Company, product AS Products FROM ranked;
注意事项
- 所有代码默认原始
Products字段的分隔符为;(分号+空格),如实际分隔符仅为分号无空格,修改代码中的分隔符参数为;即可 - 拆分后的产品顺序默认与原始字段书写顺序一致,如需调整排序规则,修改窗口函数的
ORDER BY参数即可
内容的提问来源于stack exchange,提问作者miltenburger
相关产品推荐
相关产品推荐

