MySQL 按同级表中的匹配项数量排序

Question

我有一个项目表和一个可以附加到这些项目的标签表。从更基本的角度来看，它就像 StackOverflow 问题和标签，将项目视为问题和标签以及 StackOverflow 标签。

每个项目都可以标记无限个标签，但我想按照匹配的标签数量的顺序返回结果。

我会给你我的表结构然后一个例子。

表标签

id | tag_name
 1   delicious
 2   spicy
 3   sweet

表item_tags

id | item_id | tag_id | created
 1   1         1        TIMESTAMP
 2   1         2        TIMESTAMP
 3   2         1        TIMESTAMP
 4   2         2        TIMESTAMP
 5   2         3        TIMESTAMP

正如您所看到的，如果我搜索标签“甜、辣、美味”，则项目 2 将匹配所有三个标签，而项目 1 将仅匹配最后两个标签。我显然希望首先返回项目 2，因为它的匹配标签数量较多。

所以如果我有这样的查询:

SELECT * FROM item_tags WHERE tag_id IN(1,2,3) GROUP BY item_id ORDER BY NumberOfMatches

其中 NumberOfMatches 是 item_tags 表中匹配的标签数量。

希望在没有 UNION 或任何重大内容的情况下做到这一点，但对所有建议持开放态度。

Answer 1

你们都已经到了，试试这个:

select
    it.item_id
from item_tags it
join tags t on it.tag_id = t.id
where t.id in (1, 2, 3)
group by it.item_id
order by count(distinct t.id) desc

SQLFiddle demo here

如果您想通过tag_name获取记录，则必须使用join，例如:

select
    it.item_id
from item_tags it
join tags t on it.tag_id = t.id
where t.tag_name in ('sweet', 'pic', 'delicious')
group by it.item_id
order by count(distinct t.id) desc

或者如果您知道确切的标签 ID，@Daerik 的方法会更有效。而关于性能，这里两种解决方案可能没有太大差异。