SQL:过滤一对多查询

Question

我的DB中有一个多对多的关系(但对于我的问题，它也可能是一对多的，因此得名)，它用标签链接文件。用户可以使用标记作为过滤器来搜索文件。

例如：

+ (a AND b) - c

意味着我想要用标签a和标签b标记的所有文件，但不想要用标签c标记的那些文件。

在伪代码中，这将类似于：

getFiles(filter) {
let matching_files = [];
for file in files {
let tags = file.tags;
if filter.match(tags) { // something non-trivial
matching_files.push(file);
}
}
matching_files
}

我刚刚意识到，我想要做的不是基于多个单独的行，而是基于一组行的搜索，这并不是SQL真正的目的。

我对如何实现这一点有了一个想法，那就是基于搜索查询生成动态intersect/except。因此，前面的查询类似于：

(SELECT file_id from files, tags WHERE tags.id = a) INTERSECT 
(SELECT file_id from files, tags WHERE tags.id = b) /* <- this handles + (a AND b) */ EXCEPT 
(SELECT file_id from files, tags WHERE tags.id = c) -- this one handles the - c

因此，+组将是一堆并集，AND关键字将生成INTERSECTs，OR可以是该组的WHERE子句中的一个简单的OR，而-将是之前过滤的所有内容的EXCEPT。

所以我的问题是，有没有一种更简单的方法来做到这一切？因为第一，这看起来很粗糙，第二，当文件数量增加时，这看起来根本没有效率，因为我会在所有文件中查询每个组的。解决方案不一定是SQL(如果有其他类型的数据存储可以更好地处理这一问题)，但请注意，我这样做是为了桌面应用程序(所以使用我可以嵌入程序的东西会很好)。谢谢

Answer 1

首先，在查询(如tags.file_id = files.file_id)中会漏掉一个JOIN条件。您可以使用EXISTS和NOT EXISTS来执行相同的工作。

SELECT file_id 
FROM files 
WHERE EXISTS (
SELECT 1 FROM tags 
WHERE tags.file_id = files.file_id and tags.id = a 
) and EXISTS (
SELECT 1 FROM tags 
WHERE tags.file_id = files.file_id and tags.id = b 
) and NOT EXISTS (
SELECT 1 FROM tags 
WHERE tags.file_id = files.file_id and tags.id = a 
)

如果你有一个像+(a或b)-c这样的过滤器，那么它可以用下面的sql来表示

SELECT file_id 
FROM files 
WHERE EXISTS (
SELECT 1 FROM tags 
WHERE tags.file_id = files.file_id and tags.id IN (a, b) 
) and NOT EXISTS (
SELECT 1 FROM tags 
WHERE tags.file_id = files.file_id and tags.id = a 
)

效率主要与适当的指标有关。如果您创建了像tags(id, file_id)这样的索引，那么它应该是可以的(因为您可能已经在file(file_id)上创建了一个)。

Answer 2

如果您想使用intersect，您可以执行：

select file_id
from file_tags
where file_tags.tag_id = a
intersect
select file_id
from file_tags
where file_tags.tag_id = b
intersect
select file_id
from file_tags
where file_tags.tag_id = c;

我通常会使用group by和having:

select file_id
from file_tags
where tag_id in (a, b, c)
having count(*) = 3;