mysql - 在关系数据库中查询集合

Question

如果存在一组项目，我想查询关系数据库。

我正在建模的数据具有以下形式:

key1 = [ item1, item3, item5 ]
key2 = [ item2, item7 ]
key3 = [ item2, item3, item4, item5 ]
...

我将它们存储在具有以下模式的表中

CREATE TABLE sets (key INTEGER, item INTEGER);

因此，例如，以下插入语句将插入上述三个集合。

INSERT INTO sets VALUES ( key1, item1 );
INSERT INTO sets VALUES ( key1, item3 );
INSERT INTO sets VALUES ( key1, item5 );
INSERT INTO sets VALUES ( key2, item2 );
INSERT INTO sets VALUES ( key2, item7 );
INSERT INTO sets VALUES ( key3, item2 );
INSERT INTO sets VALUES ( key3, item3 );
INSERT INTO sets VALUES ( key3, item4 );
INSERT INTO sets VALUES ( key3, item5 );

给定一组项目，如果它存储在表中，我想要与该组关联的键，如果没有，我想要 NULL。是否可以使用 sql 查询来执行此操作？如果是，请提供详细信息。

可能相关的详细信息:

• 我主要对数据库设计/查询策略感兴趣，尽管我最终会在 MySQL 中实现它，并使用 mysql-python 包在 python 中执行查询。
• 如果不同的布局对此类查询更方便，我可以自由地重构数据库模式。
• 每个集合(如果存在)都应该是唯一的。
• 我对部分匹配不感兴趣。
• 数据库规模约为 < 1000 个集合，每个集合包含 < 10 个项目，因此此时的性能不是优先考虑的问题。

提前致谢。

Answer 1

我不会评论是否有更适合这样做的模式(很有可能)，但是对于具有列 name 和 item 的模式，以下查询应该有效。 (mysql语法)

SELECT k.name
FROM (SELECT DISTINCT name FROM sets) AS k
INNER JOIN sets i1 ON (k.name = i1.name AND i1.item = 1)
INNER JOIN sets i2 ON (k.name = i2.name AND i2.item = 3)
INNER JOIN sets i3 ON (k.name = i3.name AND i3.item = 5)
LEFT JOIN sets ix ON (k.name = ix.name AND ix.item NOT IN (1, 3, 5))
WHERE ix.name IS NULL;

我们的想法是，我们在k 中拥有所有设置键，然后我们将它们与sets 中的设置项数据连接一次，对于集合中的每个设置项，我们正在寻找，在这种情况下是三个。表别名 i1、i2 和 i3 的三个内部联接中的每一个都过滤掉所有不包含搜索项的集合名称加入。最后，我们使用表别名 ix 与 sets 进行了左连接，它引入了集合中的所有额外项目，即我们没有搜索的每个项目。 ix.name 在没有找到额外项的情况下为 NULL，这正是我们想要的，因此 WHERE 子句。如果找到集合，查询将返回包含集合键的行，否则不返回任何行。

---

编辑:collapsars 答案背后的想法似乎比我的要好得多，所以这里有一个带有解释的简短版本。

SELECT sets.name
FROM sets
LEFT JOIN (
    SELECT DISTINCT name
    FROM sets
    WHERE item NOT IN (1, 3, 5)
) s1
ON (sets.name = s1.name)
WHERE s1.name IS NULL
GROUP BY sets.name
HAVING COUNT(sets.item) = 3;

这里的想法是子查询 s1 选择包含我们正在寻找的项目以外的项目的所有集合的键。因此，当我们用 s1 加入 sets 时，s1.name 是 NULL 当集合只包含我们的项目时正在寻找。然后我们按集合键分组并过滤掉任何包含错误项目数的集合。然后我们只剩下只包含我们正在搜索的项目并且长度正确的集合。由于集合只能包含一个项目一次，因此只能有一个集合满足该条件，这就是我们正在寻找的那个。

---

编辑:我突然想到如何在不排除的情况下执行此操作。

SELECT totals.name
FROM (
    SELECT name, COUNT(*) count
    FROM sets
    GROUP BY name
) totals
INNER JOIN (
    SELECT name, COUNT(*) count
    FROM sets
    WHERE item IN (1, 3, 5)
    GROUP BY name
) matches
ON (totals.name = matches.name)
WHERE totals.count = 3 AND matches.count = 3;

第一个子查询查找每个集合中项目的总数，第二个子查询查找每个集合中匹配项目的计数。当 matches.count 为 3 时，集合中有我们要查找的所有项目，如果 totals.count 也为 3，则集合中没有任何额外的项目项目。