选择尚未为某些列值创建的记录

Question

有两张桌子：

content_term
    cid | tid
    1 | 1
    1 | 2
    1 | 3
    2 | 4
    2 | 5
    2 | 6

term_group
    tid | gid
    1 | 1
    2 | 2
    3 | 3
    4 | 1
    5 | 2
    6 | 3

如何通过"tid“选择没有链接到某些"gid”的"cid“？

UPD我试过禤浩焯的方法，按cid分组：

SELECT cid 
FROM   content_term ct 
WHERE  NOT EXISTS (SELECT tid 
               FROM   term_group tg 
               WHERE  tg.tid = ct.tid 
                      AND tg.gid = 4) GROUP BY cid;

同时也尝试了禤浩焯的分组方式：

SELECT cid FROM content_term
    LEFT JOIN term_group ON (content_term.tid = term_group.tid AND gid = 4)
    WHERE gid IS NULL GROUP BY cid;

在"gid“=3或任何其他值上--它们都返回：

cid
1
2

Answer 1

这是：

SELECT DISTINCT cid FROM content_term
    JOIN term_group ON (content_term.tid = term_group.tid AND gid = 4);

将使用gid 4映射所有cid。我们现在需要content_term中所有不在此列表中的cid。因此，我们在唯一的cids和刚刚检索到的附加cids之间使用了一个LEFT JOIN：

SELECT unique_cids.cid FROM ( SELECT DISTINCT cid FROM content_term ) AS unique_cids
    LEFT JOIN ( SELECT DISTINCT cid FROM content_term
       JOIN term_group ON (content_term.tid = term_group.tid AND gid = 4) )
           AS attached_cids ON (unique_cids.cid = attached_cids.cid)
    WHERE attached_cids.cid IS NULL;

测试：

CREATE TABLE content_term ( cid integer, tid integer );
CREATE TABLE term_group ( tid integer, gid integer );
INSERT INTO content_term VALUES (1,1),(1,2),(1,3),(2,4),(2,5),(2,6);
INSERT INTO term_group VALUES (1,1),(2,2),(3,3),(4,1),(5,2),(6,3);

第一次尝试使用gid 4：

SELECT unique_cids.cid FROM ( SELECT DISTINCT cid FROM content_term )
   AS unique_cids
LEFT JOIN ( SELECT DISTINCT cid FROM content_term        
JOIN term_group ON (content_term.tid = term_group.tid AND gid = 4)) 
          AS attached_cids ON (unique_cids.cid = attached_cids.cid)
     WHERE attached_cids.cid IS NULL;

+------+
| cid  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

gid 3重试：

SELECT unique_cids.cid FROM ( SELECT DISTINCT cid FROM content_term ) 
    AS unique_cids
     LEFT JOIN ( SELECT DISTINCT cid FROM content_term
        JOIN term_group ON (content_term.tid = term_group.tid AND gid = 3))
            AS attached_cids ON (unique_cids.cid = attached_cids.cid)
     WHERE attached_cids.cid IS NULL;

Empty set (0.00 sec)

Answer 2

SELECT cid 
FROM   content_term ct 
WHERE  NOT EXISTS (SELECT tid 
                   FROM   term_group tg 
                   WHERE  tg.tid = ct.tid 
                          AND tg.gid = 4)