mysql - 如何修改此查询以按匹配项排序？

Question

此查询根据文章表中是否具有与给定关键字列表匹配的标签来获取行。我想按与搜索匹配的标签数量对结果进行排序。我该怎么做？

SELECT DISTINCT a.*, 
FROM   `article` a
INNER JOIN article_tags AS at ON at.article_id = a.article_id
INNER JOIN tags AS t ON t.tag_id = at.tag_id
WHERE  t.keyword IN ("apples", "bananas", "oranges")

|--- Article Tags ---|
|--article_tag_id----|
|--article_id--------|
|-------tag_id-------|
|--------------------|

|-------Tags---------|
|------tag_id--------|
|------keyword-------|
|--------------------|

Answer 1

你可以在行中使用一些东西

SELECT a.*, COUNT(t.keyword) as matches
FROM   `article` a
INNER JOIN article_tags AS at ON at.article_id = a.article_id
INNER JOIN tags AS t ON t.tag_id = at.tag_id
WHERE  t.keyword IN ("apples", "bananas", "oranges")
GROUP BY a.article_id
ORDER BY COUNT(t.keyword) DESC;

如果您只想要不同的关键字，则使用

SELECT a.*, COUNT(DISTINCT t.keyword) as matches
FROM   `article` a
INNER JOIN article_tags AS at ON at.article_id = a.article_id
INNER JOIN tags AS t ON t.tag_id = at.tag_id
WHERE  t.keyword IN ("apples", "bananas", "oranges")
GROUP BY a.article_id
ORDER BY COUNT(DISTINCT t.keyword) DESC;