sql - 如何显示相关(建议)关键字？

Question

我们通过分配图像（关键字）来为图像分类程序。

图像具有以下关键字“ John”，“ Jane”，“ Portrait”。

另一个图像具有以下关键字“ John”，“ Jane”，“ Wedding”。

当用户尝试对下一张图像进行分类并添加“ John”关键字时，我们希望在“建议”区域中显示关键字“ Jane”。

我们如何根据最可能的组合提取（使用SQL）建议的关键字？

编辑开始：

除了上面等待“ Jane”关键字示例的“ John”之外，我认为更理论上的表述是我们希望在分配的过去图像中找到最上面的“ x”关键字以及已经分配的“ y”关键字

：编辑结束

请注意，图片可以分配更多的关键字，最好在“建议”区域中提供更多建议。

相关表称为TagsTree，它具有以下创建DDL：

CREATE TABLE TagsTree(ImageID INTEGER NOT NULL, TagID INTEGER NOT NULL, UNIQUE (ImageID, TagID))

...其中ImageID是相片实体的FK，TagID是关键字实体的FK引用。

而且，非常感谢关于模式等的任何其他提示。

PS：如果重要的话，数据库引擎是SQLite3。

Answer 1

我认为可能是以下情况。例如，我们将为TagId=$SearchedTagID$找到建议。首先，我们应该选择与TagID具有相同ImageId的所有$SearchedTagID$，然后按TagId分组，并按每个TagId的出现次数对结果集进行排序。您也可以将结果限制为某个值（在本示例中为5个常见建议（LIMIT 5））。

SELECT TagID,
       COUNT(*) as CommonCount
FROM TagsTree
WHERE ImageID IN (SELECT ImageID FROM TagsTree WHERE TagID = $SearchedTagID$)
      AND TagID <> $SearchedTagID$
GROUP BY TagID
ORDER BY CommonCount DESC
LIMIT 5