如何按位置关联MySQL中两个竖线分隔表的产品与对应价格

针对你提到的2500条特殊格式记录，以下是三种可行的处理方案：

方法1：MySQL原生递归CTE拆分关联 #

适用于MySQL 8.0及以上版本，通过递归CTE将竖线分隔字符串拆分为带位置标记的行，再按账户ID和位置关联产品与价格：

-- 递归拆分产品字符串，生成带位置的行
WITH RECURSIVE product_items AS (
    SELECT 
        account_id,
        SUBSTRING_INDEX(product_str, '|', 1) AS product_name,
        SUBSTRING(product_str, LOCATE('|', product_str) + 1) AS remaining_str,
        1 AS position
    FROM Products
    WHERE product_str IS NOT NULL AND product_str != ''
      AND account_id IN (/* 填入需要处理的2500个账户ID */)
    UNION ALL
    SELECT 
        account_id,
        SUBSTRING_INDEX(remaining_str, '|', 1) AS product_name,
        SUBSTRING(remaining_str, LOCATE('|', remaining_str) + 1) AS remaining_str,
        position + 1
    FROM product_items
    WHERE remaining_str IS NOT NULL AND remaining_str != ''
),
-- 递归拆分价格字符串，生成带位置的行
price_items AS (
    SELECT 
        account_id,
        SUBSTRING_INDEX(price_str, '|', 1) AS price,
        SUBSTRING(price_str, LOCATE('|', price_str) + 1) AS remaining_str,
        1 AS position
    FROM Prices
    WHERE price_str IS NOT NULL AND price_str != ''
      AND account_id IN (/* 填入需要处理的2500个账户ID */)
    UNION ALL
    SELECT 
        account_id,
        SUBSTRING_INDEX(remaining_str, '|', 1) AS price,
        SUBSTRING(remaining_str, LOCATE('|', remaining_str) + 1) AS remaining_str,
        position + 1
    FROM price_items
    WHERE remaining_str IS NOT NULL AND remaining_str != ''
)
-- 关联产品与价格
SELECT 
    p.account_id,
    p.product_name,
    pr.price
FROM product_items p
INNER JOIN price_items pr 
    ON p.account_id = pr.account_id 
    AND p.position = pr.position;

方法2：导入SQL Server处理 #

利用SQL Server的字符串拆分特性简化操作，分版本处理：

版本1：SQL Server 2022及以上（支持STRING_SPLIT的ordinal参数） #

直接通过内置函数获取拆分后的位置，无需递归：

SELECT 
    p.account_id,
    p.product_name,
    pr.price
FROM (
    SELECT 
        account_id,
        value AS product_name,
        ordinal AS position
    FROM Products
    CROSS APPLY STRING_SPLIT(product_str, '|', 1) -- 1表示返回位置序号
    WHERE product_str IS NOT NULL AND product_str != ''
      AND account_id IN (/* 填入目标账户ID */)
) p
INNER JOIN (
    SELECT 
        account_id,
        value AS price,
        ordinal AS position
    FROM Prices
    CROSS APPLY STRING_SPLIT(price_str, '|', 1)
    WHERE price_str IS NOT NULL AND price_str != ''
      AND account_id IN (/* 填入目标账户ID */)
) pr 
    ON p.account_id = pr.account_id 
    AND p.position = pr.position;

版本2：SQL Server 2016-2019（无ordinal参数） #

借助数字表生成位置序号：

-- 先确保存在数字表（可临时生成或创建永久表）
WITH Numbers AS (
    SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.columns
),
product_split AS (
    SELECT 
        account_id,
        SUBSTRING(SUBSTRING(product_str, 1, CHARINDEX('|', product_str + '|', n) - 1), 1, 255) AS product_name,
        n AS position
    FROM Products
    JOIN Numbers n 
        ON n <= LEN(product_str) - LEN(REPLACE(product_str, '|', '')) + 1
    WHERE product_str IS NOT NULL AND product_str != ''
      AND account_id IN (/* 填入目标账户ID */)
),
price_split AS (
    SELECT 
        account_id,
        SUBSTRING(SUBSTRING(price_str, 1, CHARINDEX('|', price_str + '|', n) - 1), 1, 255) AS price,
        n AS position
    FROM Prices
    JOIN Numbers n 
        ON n <= LEN(price_str) - LEN(REPLACE(price_str, '|', '')) + 1
    WHERE price_str IS NOT NULL AND price_str != ''
      AND account_id IN (/* 填入目标账户ID */)
)
SELECT 
    p.account_id,
    p.product_name,
    pr.price
FROM product_split p
INNER JOIN price_split pr 
    ON p.account_id = pr.account_id 
    AND p.position = pr.position;

方法3：SSIS数据流处理 #

通过可视化组件完成拆分与关联，适合不熟悉复杂SQL的场景：

1. 数据源配置：添加两个OLE DB源，分别连接MySQL的Products和Prices表，通过筛选条件（如account_id列表）仅读取需要处理的2500条记录。
2. 字符串拆分：对每个数据源添加脚本组件（转换类型），在脚本中拆分字符串并输出带位置的行（以C#为例）：

   public override void Input0_ProcessInputRow(Input0Buffer Row)
   {
       if (!Row.product_str_IsNull && !string.IsNullOrEmpty(Row.product_str))
       {
           string[] products = Row.product_str.Split('|');
           for (int i = 0; i < products.Length; i++)
           {
               Output0Buffer.AddRow();
               Output0Buffer.account_id = Row.account_id;
               Output0Buffer.product_name = products[i];
               Output0Buffer.position = i + 1;
           }
       }
   }
   

   价格表的脚本逻辑完全一致，仅需修改字段名。
3. 排序与关联：对拆分后的产品和价格数据流分别添加排序组件，按account_id和position排序；再添加合并连接组件，以account_id和position为关联键合并两个数据集。
4. 输出结果：将合并后的数据流写入目标表（MySQL或SQL Server均可）。

内容的提问来源于stack exchange，提问作者A.Steer