sql - 从 SQL 数据库中选择具有相似标签(多对多关系)的行

Question

我的实体表和标签通过另一个表绑定(bind)到它(多对多关系)。

我选择了一个实体，我的目标是找到一组具有尽可能多的相似标签的实体。该集合必须按“相似性”排序——每个实体与所选实体共享的标签数量。如果标签具有相同的 ID，则它们是相似的。

我想知道是否有优雅且快速的方法可以通过单个查询完成此操作。

我现在看到的唯一解决方案是获取所有标签实体关系并在我的应用程序中计算它们的相似性，然后进行另一个数据库查询以选择我计算的内容，但它看起来不太优雅。

数据库结构:

实体ID...

标签ID姓名

实体标签实体编号tag_id

更新:MySQL 的最终解决方案。

所以我有绘画、标签和 painting_tag 关系表。此查询获取相似的绘画及其与先前选择的绘画的“相似性指数”。

SELECT site_painting.*, Count(tr.tag_id) as similarity
From site_painting_tag_relation as tr
Inner Join site_painting_tag_relation as tr2 ON ( tr2.tag_id = tr.tag_id and tr2.painting_id = :id )
Left join site_painting on site_painting.id=tr.painting_id
Where tr.painting_id <> :id
Group By  tr.painting_id
Having Count(*) > 0
Order By  Count(*) DESC, tr.painting_id limit 1

Answer 1

好的，关于数据库结构的信息很有帮助 --

entity id ...

entity_tag entity_id tag_id

tag id name

让我们看一些示例值--

entity
  id=100
  id=...
  id=199

entity_tag
  100, 3
  100, 5
  101, 1
  102, 7
  ...
  199, 3
  199, 7

tag
  id=1
  id=...
  id=10

因此，如果我们对我们拥有的 entity_tag 进行反规范化

  100   3,5
  101  1
  102       7
  199   3   7

与其余部分的相似度指数为 199，范围为 0 到 10

  100   1 in common
  101   0 in common
  102   1 in common
  199 self, no comparison

如果我没记错的话，我们要显示 100 和 102 是最高的，努？？

这是对 SQL 的一个尝试——它可能类似于 ---

SELECT  TOP 10
FROM
(SELECT
   allET.EID,
   Count(*) as Similarity


 From entity_tag as allET

 Left Join 
      (Select * From  entity_tag  Where EID = myEID ) as myET
 On allET.TID = myET.TID

 Where allET.EID <> myEID

 Group By  allET.EID

 Having Count(*) > 0

 Order By  Count(*) DESC, allET.EID
)