sqlite - 返回在列中具有完全相同项目的不同名称对

Question

我想在表中找到不同的名称对，这些名称在项目列中具有完全相同的项目。例如:

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

这里的答案是 Alice,Bob 因为他们拿走了完全相同的元素。

我只想用双重否定(使用 NOT EXISTS/NOT IN)来做这件事，我认为这更适合这个问题，但我无法想出任何与功能相差甚远的东西。

这有点类似于this问题 但我正在使用 SQLite，所以我不能使用 GROUP_CONCAT() 但我想知道如何使用 NOT EXISTS/NOT IN 使用关系划分来完成。

Answer 1

要获取所有名称对之间的共同项目数，您可以使用以下查询:

SELECT t1.name AS name1, t2.name AS name2, COUNT(*) AS cnt
FROM t AS t1
INNER JOIN t AS t2 ON t1.item = t2.item AND t1.name < t2.name
GROUP BY t1.name, t2.name

输出:

name1   name2       cnt
------------------------
Alice   Bob         3
Alice   Charlie     2
Bob     Charlie     2

现在您想要的只是过滤掉计数不等于 name1 和 name2 的项数的 (name1, name2) 对。您可以使用带有相关子查询的 HAVING 子句来执行此操作:

SELECT t1.name AS name1, t2.name AS name2
FROM t AS t1
INNER JOIN t AS t2 ON t1.item = t2.item AND t1.name < t2.name
GROUP BY t1.name, t2.name
HAVING COUNT(*) = (SELECT COUNT(*) FROM t WHERE name = t1.name) AND 
       COUNT(*) = (SELECT COUNT(*) FROM t WHERE name = t2.name)

Demo here