如何用CTE关联无关联表的SELECT结果批量插入关联表？

核心思路 #

因为两张源表没有关联关系，要实现所有目标员工与目标分类的配对，使用**交叉连接（CROSS JOIN）**可以自动生成所有可能的组合，再结合CTE封装两个查询逻辑，就能实现一键插入。

基础版SQL（不处理重复） #

如果不需要担心employees_categories表中已有重复的关联记录，可以直接使用以下代码：

WITH london_employees AS (
    SELECT employee_id FROM employees
    WHERE city = 'London'
),
dairy_categories AS (
    SELECT category_id FROM categories
    WHERE category_name ILIKE 'dairy%'
)
INSERT INTO employees_categories (employee_id, category_id)
SELECT le.employee_id, dc.category_id
FROM london_employees le
CROSS JOIN dairy_categories dc;

这段代码会将所有伦敦员工的employee_id与所有匹配dairy%的category_id逐一配对，插入到关联表中。

防重复版SQL #

如果需要避免插入已存在的关联记录，可以根据表结构选择以下两种方式：

方式1：利用唯一约束（推荐） #

如果employees_categories表已创建(employee_id, category_id)的唯一约束，使用ON CONFLICT语法跳过重复记录：

WITH london_employees AS (
    SELECT employee_id FROM employees
    WHERE city = 'London'
),
dairy_categories AS (
    SELECT category_id FROM categories
    WHERE category_name ILIKE 'dairy%'
)
INSERT INTO employees_categories (employee_id, category_id)
SELECT le.employee_id, dc.category_id
FROM london_employees le
CROSS JOIN dairy_categories dc
ON CONFLICT (employee_id, category_id) DO NOTHING;

方式2：用NOT EXISTS过滤 #

如果没有唯一约束，通过子查询检查已存在的记录：

WITH london_employees AS (
    SELECT employee_id FROM employees
    WHERE city = 'London'
),
dairy_categories AS (
    SELECT category_id FROM categories
    WHERE category_name ILIKE 'dairy%'
)
INSERT INTO employees_categories (employee_id, category_id)
SELECT le.employee_id, dc.category_id
FROM london_employees le
CROSS JOIN dairy_categories dc
WHERE NOT EXISTS (
    SELECT 1 FROM employees_categories ec
    WHERE ec.employee_id = le.employee_id
      AND ec.category_id = dc.category_id
);

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