SQL多表关联插入时如何避免重复Id？求助解决方案

有表A和表B如下，需要创建新表C，满足以下条件：

• A和B中存在相同email
• A的created_date与B的date年份、季度相同
  将符合条件的A.Id、A.email、A.created_date、B.reason插入到C中。

使用INNER JOIN关联时，出现重复的A.Id，需要解决该问题。

表A #

表B #

重复Id的核心原因是：同一个A的记录（对应唯一Id）可能匹配到B中多条符合年季度条件的记录，导致插入多条相同Id的记录。根据业务需求，有两种处理方式：

1. 保留所有符合条件的记录，但过滤重复组合 #

如果允许一个Id对应多个reason，但要避免完全重复的(Id, email, created_date, reason)组合，用DISTINCT过滤：

INSERT INTO C (Id, email, created_date, reason)
SELECT DISTINCT 
    a.Id, 
    a.email, 
    a.created_date, 
    b.reason
FROM a
INNER JOIN b 
    ON a.email = b.email
    -- 按数据库类型选择对应的年季度提取函数
    -- MySQL: YEAR(a.created_date) = YEAR(b.date) AND QUARTER(a.created_date) = QUARTER(b.date)
    -- PostgreSQL: EXTRACT(YEAR FROM a.created_date) = EXTRACT(YEAR FROM b.date) AND EXTRACT(QUARTER FROM a.created_date) = EXTRACT(QUARTER FROM b.date)
    -- SQL Server: DATEPART(YEAR, a.created_date) = DATEPART(YEAR, b.date) AND DATEPART(QUARTER, a.created_date) = DATEPART(QUARTER, b.date)
    AND YEAR(a.created_date) = YEAR(b.date)
    AND QUARTER(a.created_date) = QUARTER(b.date)

2. 每个Id只保留一条记录 #

如果业务要求每个Id在C中仅出现一次，需要对B的匹配记录做聚合，以下是两种常用方式：

方式一：取每个Id对应的任意reason（如最大/最小） #

INSERT INTO C (Id, email, created_date, reason)
SELECT 
    a.Id, 
    a.email, 
    a.created_date, 
    MAX(b.reason) -- 替换为MIN(b.reason)可取最小的reason，按需选择
FROM a
INNER JOIN b 
    ON a.email = b.email
    AND YEAR(a.created_date) = YEAR(b.date)
    AND QUARTER(a.created_date) = QUARTER(b.date)
GROUP BY a.Id, a.email, a.created_date

方式二：取同季度最新的B记录对应的reason #

-- 先给B的同用户同年季度的记录排序，取最新的一条
WITH ranked_b AS (
    SELECT 
        email, 
        date, 
        reason,
        ROW_NUMBER() OVER (
            PARTITION BY email, YEAR(date), QUARTER(date) 
            ORDER BY date DESC
        ) AS rn
    FROM b
)
INSERT INTO C (Id, email, created_date, reason)
SELECT 
    a.Id, 
    a.email, 
    a.created_date, 
    rb.reason
FROM a
INNER JOIN ranked_b rb
    ON a.email = rb.email
    AND YEAR(a.created_date) = YEAR(rb.date)
    AND QUARTER(a.created_date) = QUARTER(rb.date)
WHERE rb.rn = 1 -- 仅取最新的一条记录

原代码中直接使用A.quarter和a.year是错误的，因为表A没有这两个字段，必须通过日期函数从created_date中提取年和季度，不同数据库的函数语法略有差异，需根据实际使用的数据库调整。

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