（一张表）插入行 [英] (One table) insert rows

问题描述

这个问题是这个问题的延续。

我有下表 egr ：

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

我想插入offid 2没有的缺少的groupid（与offid 1相比）。结果将是：

I would like to insert missing groupids that the offid 2 does not have (compared to offid 1). Result would be:

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

我的尝试是基于我对其他问题的回答（不起作用）：

My try, based on the answer of my other question (not working):

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 
                 );

推荐答案

使用<$ c可能更容易实现$ c>除运算符：

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

这篇关于（一张表）插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！