我想在表中找到不同的名称对,它们在items列中具有相同的确切项.例如：
CREATE TABLE t
(
name VARCHAR(255),item VARCHAR(255)
);
INSERT INTO t VALUES("Alice","Orange");
INSERT INTO t VALUES("Alice","Pear");
INSERT INTO t VALUES("Alice","Lemon");
INSERT INTO t VALUES("Bob","Orange");
INSERT INTO t VALUES("Bob","Pear");
INSERT INTO t VALUES("Bob","Lemon");
INSERT INTO t VALUES("Charlie","Pear");
INSERT INTO t VALUES("Charlie","Lemon");
这里的答案是爱丽丝,鲍勃,因为他们采取完全相同的项目.
我想用双重否定(使用NOT EXISTS / NOT IN)来做这件事我认为更适合这个问题,但我无法想出任何远离功能的东西.
这有点类似于this问题,但我使用sqlite,所以我不能使用GROUP_CONCAT(),但我想知道如何使用NOT EXISTS / NOT IN使用关系除法.
随着
compound queries：
SELECT t1.name,t2.name
FROM t AS t1,t AS t2
GROUP BY t1.name,t2.name
HAVING t1.name < t2.name
AND NOT EXISTS (SELECT item FROM t WHERE name = t1.name
EXCEPT
SELECT item FROM t WHERE name = t2.name)
AND NOT EXISTS (SELECT item FROM t WHERE name = t2.name
EXCEPT
SELECT item FROM t WHERE name = t1.name);
使用NOT IN是可能的,bit表示完全相同的机制,具有更高的复杂性：
SELECT t1.name,t2.name
HAVING t1.name < t2.name
AND NOT EXISTS (SELECT item
FROM t
WHERE name = t1.name
AND item NOT IN (SELECT item
FROM t
WHERE name = t2.name))
AND NOT EXISTS (SELECT item
FROM t
WHERE name = t2.name
AND item NOT IN (SELECT item
FROM t
WHERE name = t1.name));
