如何用SQL生成基于别名的产品名称所有可能组合？

问题背景 #

现有两张表：

1. main_table：包含一列（示例列名product_list），存储以分号分隔的产品名称，例如'A;B;C'、'X;Y'、'B;A;Z;T'。
2. alternate_data：存储产品与别名的映射关系，表结构及数据如下：

需求：针对main_table中每条记录的product_list，生成所有基于别名的产品组合，例如输入'A;B;C'时，需输出所有可能的别名组合（如示例中的12条结果）。

实现思路 #

核心步骤分为三步：

1. 将分号分隔的产品字符串拆分为单独的产品行，同时保留原始记录的唯一标识（需确保main_table有主键，比如id列）。
2. 关联alternate_data表，获取每个产品对应的所有别名。
3. 对同一条原始记录的所有别名进行笛卡尔积组合，再将组合后的别名拼接回分号分隔的字符串。

分数据库SQL实现 #

1. MySQL 版本 #

假设main_table有主键id，示例代码如下：

-- 递归拆分分号分隔的字符串
WITH RECURSIVE split_products AS (
    SELECT 
        id,
        product_list,
        1 AS pos,
        SUBSTRING_INDEX(product_list, ';', 1) AS product,
        SUBSTRING(product_list, LENGTH(SUBSTRING_INDEX(product_list, ';', 1)) + 2) AS remaining
    FROM main_table
    WHERE product_list IS NOT NULL AND product_list != ''
    UNION ALL
    SELECT 
        id,
        product_list,
        pos + 1,
        SUBSTRING_INDEX(remaining, ';', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ';', 1)) + 2)
    FROM split_products
    WHERE remaining IS NOT NULL AND remaining != ''
),
-- 关联别名表获取每个产品的所有别名
product_aliases AS (
    SELECT 
        sp.id,
        sp.pos,
        ad.Alternate_name
    FROM split_products sp
    JOIN alternate_data ad ON sp.product = ad.Product_name
)
-- 按原始顺序组合别名并拼接成字符串
SELECT 
    id,
    GROUP_CONCAT(Alternate_name ORDER BY pos SEPARATOR ';') AS alias_combination
FROM product_aliases
GROUP BY id, GROUP_CONCAT(pos ORDER BY pos)
ORDER BY id, alias_combination;

2. PostgreSQL 版本 #

利用string_to_array拆分字符串：

WITH split_products AS (
    SELECT 
        id,
        unnest(string_to_array(product_list, ';')) AS product,
        row_number() OVER (PARTITION BY id ORDER BY unnest(string_to_array(product_list, ';'))) AS pos
    FROM main_table
    WHERE product_list IS NOT NULL AND product_list != ''
),
product_aliases AS (
    SELECT 
        sp.id,
        sp.pos,
        ad.Alternate_name
    FROM split_products sp
    JOIN alternate_data ad ON sp.product = ad.Product_name
)
-- 按位置拼接别名组合
SELECT 
    id,
    string_agg(Alternate_name, ';' ORDER BY pos) AS alias_combination
FROM product_aliases
GROUP BY id, (SELECT array_agg(pos ORDER BY pos) FROM product_aliases pa WHERE pa.id = product_aliases.id)
ORDER BY id, alias_combination;

3. SQL Server 版本 #

使用STRING_SPLIT（需SQL Server 2016及以上版本）：

WITH split_products AS (
    SELECT 
        mt.id,
        value AS product,
        ROW_NUMBER() OVER (PARTITION BY mt.id ORDER BY (SELECT NULL)) AS pos 
        -- 注意：STRING_SPLIT默认不保证拆分顺序，需严格顺序请用递归CTE或自定义拆分函数
    FROM main_table mt
    CROSS APPLY STRING_SPLIT(mt.product_list, ';')
    WHERE mt.product_list IS NOT NULL AND mt.product_list != ''
),
product_aliases AS (
    SELECT 
        sp.id,
        sp.pos,
        ad.Alternate_name
    FROM split_products sp
    JOIN alternate_data ad ON sp.product = ad.Product_name
)
-- 聚合拼接别名组合
SELECT 
    id,
    STRING_AGG(Alternate_name, ';' WITHIN GROUP (ORDER BY pos)) AS alias_combination
FROM product_aliases
GROUP BY id
ORDER BY id, alias_combination;

注意事项 #

• 若main_table无主键，需添加唯一标识列，确保仅对同一条原始记录的别名进行笛卡尔积组合。
• SQL Server的STRING_SPLIT不保证拆分后的顺序，若需严格匹配原始字符串中的产品顺序，建议替换为递归CTE或自定义拆分函数。

内容的提问来源于stack exchange，提问作者YogeshR