SQL如何为指定Phase的ID/Sequence添加所有关联Characteristics？

现有两张表：

1. 主表（假设命名为main_table）结构及数据：

id   sequence  phase  characteristics
1      1        300      001
2      1        300      001
2      2        300      002
..

2. 特征表（假设命名为char_table）结构及数据：

phase  characteristics
300      003
300      004
300      005
....

需求：将phase=300的所有特征（包含主表原有的和特征表中的），添加到每个phase=300的id/sequence组合下，得到目标结果。

可以通过提取唯一组合 + 笛卡尔积匹配特征 + 合并原数据的方式实现，以下是通用SQL写法：

-- 保留主表原有的phase=300记录
SELECT id, sequence, phase, characteristics
FROM main_table
WHERE phase = 300

UNION ALL

-- 为每个唯一id/sequence组合匹配特征表中所有phase=300的特征
SELECT mt.id, mt.sequence, mt.phase, ct.characteristics
FROM (
    -- 提取主表中phase=300的唯一id/sequence/phase组合
    SELECT DISTINCT id, sequence, phase
    FROM main_table
    WHERE phase = 300
) mt
CROSS JOIN (
    -- 提取特征表中phase=300的所有特征
    SELECT characteristics
    FROM char_table
    WHERE phase = 300
) ct
-- 按指定字段排序，匹配预期结果顺序
ORDER BY id, sequence, characteristics;

1. 第一部分语句保留主表中原有的phase=300记录；
2. 子查询mt提取主表中phase=300的唯一id/sequence/phase组合，避免重复生成相同匹配项；
3. CROSS JOIN将每个唯一组合与特征表的phase=300特征做笛卡尔积，生成新增的关联记录；
4. 使用UNION ALL而非UNION，避免因特征值重复导致的记录丢失；
5. 最后通过ORDER BY保证结果顺序与预期一致。

如果你的数据库支持CTE（公共表表达式），也可以用更易读的写法：

WITH unique_combinations AS (
    SELECT DISTINCT id, sequence, phase
    FROM main_table
    WHERE phase = 300
),
phase_300_chars AS (
    SELECT characteristics
    FROM char_table
    WHERE phase = 300
)
SELECT id, sequence, phase, characteristics
FROM main_table
WHERE phase = 300
UNION ALL
SELECT uc.id, uc.sequence, uc.phase, pc.characteristics
FROM unique_combinations uc
CROSS JOIN phase_300_chars pc
ORDER BY id, sequence, characteristics;

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