sqlite – 返回在列中具有相同精确项的不同名称对

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使用关系除法.

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!