按出现次数对结果排序
我有以下两个表。
书签标签(书签ID、标签ID) 标签(TagID、标题)
目前,我正在选择具有适当 BookmarkID 的所有标签。问题是我只想选择标签一次以避免结果重复,并且只返回出现次数最多的标签。
这是我当前的 SQL 查询:
SELECT Tag.Title
FROM `Tag` INNER JOIN BookmarkTag
WHERE BookmarkTag.BookmarkID = 1 AND Tag.TagID = BookmarkTag.TagID'
I have the following two tables.
BookmarkTag ( BookmarkID, TagID )
Tag ( TagID, Title)
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 that occur the most.
This is my current SQL query:
SELECT Tag.Title
FROM `Tag` INNER JOIN BookmarkTag
WHERE BookmarkTag.BookmarkID = 1 AND Tag.TagID = BookmarkTag.TagID'
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您需要将连接条件放在
JOIN
关键字后面的ON
子句中。 不在where
子句中。您将 SQL89 与 SQL92 语法混合在一起。这可能有效,我没有测试过,但这更快。
为了使每个标签的结果都是唯一的,请对 tagid 执行
group by
。然后,您可以使用 count(*) 按出现次数排序,以使出现次数最多的标签浮到顶部。
(尝试始终使用
count(*)
因为它比count(afield)
更快)You need to put the join condition in an
ON
clause after theJOIN
keyword. Not in thewhere
clause.You were mixing SQL89 with SQL92 syntax. This may work I haven't tested, but this is faster.
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 thancount(afield)
)如果您想获取 7 个最受欢迎的标题,请在查询末尾添加
LIMIT 7
或任何其他数字。我的版本将按照受欢迎程度的降序生成所有内容。If You want to get a 7 most popular titles, add
LIMIT 7
at the end of query, or any other number. My version will generate all in descending order of their popularity.