sql - (一张表)插入行

Question

这个问题是this one的延续.

我有下表egr:

+---------+------------+
|  offid  |  groupid   |
+---------+------------+
|       1 | 101        |
|       1 | 202        |
|       2 | 202        |
|       2 | 404        |
+---------+------------+

我想插入 offid 2 没有的缺失组 ID(与 offid 1 相比)。结果将是:

+---------+------------+
|  offid  |  groupid   |
+---------+------------+
|       1 | 101        |
|       1 | 202        |
|       2 | 202        |
|       2 | 404        |
|       2 | 101        |   --> new row to insert
+---------+------------+

我的尝试，基于我的其他问题的答案(无效):

INSERT INTO egr (offid, groupid)
  SELECT 2, egr1.groupid
  FROM egr AS egr1 
  WHERE egr1.offid = 1
  AND NOT EXISTS
    (select 1
                  from egr e2
                  where e2.groupid = egr1.groupid and 
                        e2.offid in (1, 2) and
                        e2.offid <> egr1.offid 
                 );

Answer 1

使用 except 运算符可能更容易实现:

INSERT INTO egr (offid, groupid)
SELECT 2, groupid
FROM   egr
WHERE  offid = 1
EXCEPT
SELECT 2, groupid
FROM   egr
WHERE  offid = 2