同一表内两ID行对比并插入缺失行的SQL问题求助

让我帮你梳理下这个问题：你想要把tab1中ID=1的行复制为ID=2的新行，但要排除那些和ID=2现有行（row5）在c1、c2、c3、c4四列完全重复的记录对吧？先明确下你的场景：

原表数据（tab1） #

id | c1 | c2 | c3       | c4
---|----|----|----------|---
1  | a  | b  | 01-02-18 | c  -- row1
1  | o  | b  | 01-02-18 | c  -- row2
1  | a  | b  | 04-05-16 | c  -- row3
1  | n  | g  | 01-02-18 | d  -- row4
2  | a  | b  | 01-02-18 | c  -- row5

需求说明 #

将ID=1的行插入为ID=2的新行，但跳过与row5（ID=2现有行）在c1、c2、c3、c4完全重复的row1，最终期望得到：

id | c1 | c2 | c3       | c4
---|----|----|----------|---
1  | a  | b  | 01-02-18 | c  -- row1
1  | o  | b  | 01-02-18 | c  -- row2
1  | a  | b  | 04-05-16 | c  -- row3
1  | n  | g  | 01-02-18 | d  -- row4
2  | a  | b  | 01-02-18 | c  -- row5
2  | o  | b  | 01-02-18 | c  -- row6
2  | a  | b  | 04-05-16 | c  -- row7
2  | n  | g  | 01-02-18 | d  -- row8

你的尝试代码问题排查 #

你写的查询和插入逻辑本身思路是对的，但可能存在几个细节问题导致不符合预期：

1. 目标表不匹配：插入语句的目标表是rsk_mdl_sec_map_ts，但你描述的原表是tab1，如果这两个表结构不一致（列数、列类型），会导致插入失败或数据异常。如果你的目标就是tab1，需要修改插入目标表。
2. ID值类型问题：你用'2'作为新ID值，如果id列是数值类型，带引号的字符串可能会触发类型转换错误，应该直接用数值2。
3. 日期列比较问题：如果c3是日期类型，字符串'01-02-18'的格式可能和数据库的日期解析规则不匹配（比如MM-DD-YY vs DD-MM-YY），导致本该匹配的重复记录没被识别。

修正后的解决方案 #

方案一：优化原有NOT EXISTS逻辑 #

如果目标表是tab1且id为数值类型，修正后的插入语句如下：

INSERT INTO tab1 (id, c1, c2, c3, c4)
SELECT 2, c1, c2, c3, c4
FROM tab1 A
WHERE A.id = 1
  AND NOT EXISTS (
      SELECT 1
      FROM tab1 B
      WHERE B.id = 2
        AND B.c1 = A.c1
        AND B.c2 = A.c2
        AND B.c3 = A.c3
        AND B.c4 = A.c4
  );

关键注意点：

• 明确指定插入列名，避免表结构变化导致插入顺序错误
• 用SELECT 1代替SELECT *，子查询效率更高（无需返回所有列）
• 若c3是日期类型，建议用数据库日期函数统一格式后再比较，比如TO_DATE(A.c3, 'MM-DD-YY') = TO_DATE(B.c3, 'MM-DD-YY')

方案二：用LEFT JOIN筛选不重复记录 #

如果NOT EXISTS逻辑调试麻烦，可以换成更直观的LEFT JOIN写法：

INSERT INTO tab1 (id, c1, c2, c3, c4)
SELECT 2, A.c1, A.c2, A.c3, A.c4
FROM tab1 A
LEFT JOIN tab1 B
  ON B.id = 2
  AND B.c1 = A.c1
  AND B.c2 = A.c2
  AND B.c3 = A.c3
  AND B.c4 = A.c4
WHERE A.id = 1
  AND B.id IS NULL;

逻辑说明：将ID=1的行与ID=2的行关联，筛选出ID=2中没有匹配的行，再插入为ID=2的新行。

方案三：用EXCEPT简化逻辑（适用于PostgreSQL/SQL Server等支持的数据库） #

如果你的数据库支持EXCEPT语法，可以用更简洁的方式获取需要插入的记录：

INSERT INTO tab1 (id, c1, c2, c3, c4)
SELECT 2, c1, c2, c3, c4
FROM (
    SELECT c1, c2, c3, c4 FROM tab1 WHERE id = 1
    EXCEPT
    SELECT c1, c2, c3, c4 FROM tab1 WHERE id = 2
) AS temp;

逻辑说明：先取ID=1的所有c1-c4组合，减去ID=2的c1-c4组合，剩下的就是需要插入的记录，再给它们加上ID=2。

建议你先单独执行每个方案中的SELECT部分，确认返回的是row2、row3、row4这三条记录，再执行插入操作，避免误插入数据。

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