mysql - sql查询根据标签判断最相似的商品

Question

我正在做一个电子商店，所以我有 3 个表:

1) 商品

id      | title
--------+----------- 
1       | Toy car
2       | Toy pony
3       | Doll

2) 标签

id      | title
--------+----------- 
1       | Toy
2       | Boys
3       | Girls

3) 链接

goods_id| tag_id
--------+----------- 
1       | 1
1       | 2
2       | 1
2       | 2
2       | 3
3       | 3

所以我需要使用这样的算法打印相关商品:使用标签获取与所选商品最相似的商品。大多数标签是相互的 - 最合适的项目是

所以 goods#1 的结果应该是:goods#2,goods#3

对于goods#2:goods#1,goods#3

对于goods#3:goods#2,goods#1

而且我不知道如何通过一次查询获得按相互标签计数排序的相似商品

Answer 1

此查询将返回具有最大公共(public)标签数的所有项目:

SET @item = 1;

SELECT
  goods_id
FROM
  links
WHERE
  tag_id IN (SELECT tag_id FROM links WHERE goods_id=@item)
  AND goods_id!=@item
GROUP BY
  goods_id
HAVING
  COUNT(*) = (
    SELECT
      COUNT(*)
    FROM
      links
    WHERE
      tag_id IN (SELECT tag_id FROM links WHERE goods_id=@item)
      AND goods_id!=@item
    GROUP BY
      goods_id
    ORDER BY
      COUNT(*) DESC
    LIMIT 1
  )

请参阅 fiddle here .

或者这个将返回所有项目，即使是那些没有共同标签的项目，按照共同标签的数量排序:

SELECT
  goods_id
FROM
  links
WHERE
  goods_id!=@item
GROUP BY
  goods_id
ORDER BY
  COUNT(CASE WHEN tag_id IN (SELECT tag_id FROM links WHERE goods_id=@item) THEN 1 END) DESC;