按出现次数排序结果 [英] Order Results By Occurrence

问题描述

我有以下两个表.

BookmarkTag ( BookmarkID, TagID )标签(标签ID，标题)

BookmarkTag ( BookmarkID, TagID ) Tag ( TagID, Title)

目前我正在选择具有适当 BookmarkID 的所有标签.问题是我只想选择标签一次以避免结果重复，并且只带回出现最多的标签.

Currently I am selecting all the tags with the appropriate BookmarkID. The problem is I want to only select the tag once to avoid duplication in the result and also only bring back the tags the occur the most.

这是我当前的 SQL 查询:

This is my current SQL query:

SELECT Tag.Title 
FROM `Tag` INNER JOIN BookmarkTag 
WHERE BookmarkTag.BookmarkID = 1 AND Tag.TagID = BookmarkTag.TagID'

推荐答案

您需要将连接条件放在 JOIN 关键字之后的 ON 子句中.不在where 子句中.
您将 SQL89 与 SQL92 语法混合使用.我还没有测试过，这可能有效，但速度更快.

You need to put the join condition in an ON clause after the JOIN keyword. Not in the where clause.
You were mixing SQL89 with SQL92 syntax. This may work I haven't tested, but this is faster.

SELECT Tag.Title 
FROM `Tag` t
INNER JOIN BookmarkTag b ON (t.tagid = b.tagid)
WHERE B.BookmarkID = 1
GROUP BY t.tagid
ORDER BY count(*) DESC

为了使每个标签的结果唯一，请对 tagid 执行 group by.
然后你可以order by使用count(*)来查看使出现次数最多的标签浮到顶部.
(尽量总是使用 count(*) 因为它比 count(afield) 快)

In order to make the results unique per tag, do a group by on tagid.
Then you can order by occurrence by using count(*) to see make the tags with the highest occurrence float to the top.
(try to always use count(*) because it is faster than count(afield) )

这篇关于按出现次数排序结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！