按出现次数对结果排序

发布于 2024-11-15 05:42:32 字数 292 浏览 7 评论 0原文

我有以下两个表。

书签标签（书签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'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

非要怀念 2024-11-22 05:42:32

您需要将连接条件放在 JOIN 关键字后面的 ON 子句中。不在 where 子句中。
您将 SQL89 与 SQL92 语法混合在一起。这可能有效，我没有测试过，但这更快。

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。
然后，您可以使用 count(*) 按出现次数排序，以使出现次数最多的标签浮到顶部。
（尝试始终使用 count(*) 因为它比 count(afield) 更快）

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

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) )

回复收藏 0 原文

幸福丶如此 2024-11-22 05:42:32

SELECT t.Title, COUNT(*) AS TitleCount
FROM BookmarkTag AS bt
INNER JOIN Tag AS t ON t.TagID = bt.TagID
GROUP BY t.Title
ORDER BY TitleCount DESC

如果您想获取 7 个最受欢迎的标题，请在查询末尾添加 LIMIT 7 或任何其他数字。我的版本将按照受欢迎程度的降序生成所有内容。

SELECT t.Title, COUNT(*) AS TitleCount
FROM BookmarkTag AS bt
INNER JOIN Tag AS t ON t.TagID = bt.TagID
GROUP BY t.Title
ORDER BY TitleCount DESC

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.

回复收藏 0 原文

~没有更多了~