You need to enable JavaScript to run this app. 如何使用SQL将表中分号分隔的同列字段拆分为同列多行
优惠活动
大模型
产品
解决方案
定价
更多

如何使用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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.16 00:54:05