如何从标准化表中获取最流行的标签?
On my website, I have three big sections: a section where people can start discussions, a section where they can share stories and a blog with articles I write myself. I've build a custom tagging system using the database structure below.
在我的网站上,我有三大部分:人们可以开始讨论的部分,他们可以分享故事的部分以及我自己写的文章的博客。我使用下面的数据库结构构建了一个自定义标记系统。
In my tags_xref table I needed to have 3 different columns for each post type, because a discussion can have the same id as a story or article.
在我的tags_xref表中,每个帖子类型需要有3个不同的列,因为讨论可以与故事或文章具有相同的ID。
Everything is working exactly as it should, but I am having problems with queries to show certain tags. i already know how to show the most popular tags in the discussion section:
一切都正常工作,但我遇到查询显示某些标签的问题。我已经知道如何在讨论部分显示最受欢迎的标签:
"SELECT t.id, t.tag, COUNT(discussion_id) AS cnt
FROM tags_xref AS xrf
INNER JOIN tags AS t ON xrf.tag_id = t.id
GROUP BY t.id, t.tag
ORDER BY COUNT(discussion_id) DESC LIMIT 20"
Now I want to show the most popular tags on the whole website, so from the discussions, stories and articles. Is there anyone who can help me with this? I have been working on this for hours and can't seem to find a solution.. Thanks!
现在我想在整个网站上显示最受欢迎的标签,所以从讨论,故事和文章。有没有人可以帮我这个?我已经工作了几个小时,似乎无法找到解决方案..谢谢!
1 个解决方案
#1
4
You can do a simple modification to your query:
您可以对查询进行简单的修改:
SELECT t.id, t.tag, COUNT(*) AS cnt
FROM tags_xref xrf INNER JOIN
tags t
ON xrf.tag_id = t.id
GROUP BY t.id, t.tag
ORDER BY COUNT(*) DESC
LIMIT 20;
Presumably, the various ids are NULL
when they are not appropriate. If, for some strange reason, you actually stored values in all three ids for a given xref, you can do:
据推测,当它们不合适时,各种id都是NULL。如果出于某些奇怪的原因,您实际上为给定的外部参照存储了所有三个ID中的值,您可以执行以下操作:
SELECT t.id, t.tag,
(COUNT(story_id) + COUNT(discussion_id) + COUNT(article_id) ) AS cnt
FROM tags_xref xrf INNER JOIN
tags t
ON xrf.tag_id = t.id
GROUP BY t.id, t.tag
ORDER BY cnt DESC
LIMIT 20;
更多相关文章
- 连接相关标签的最佳数据库设计
- 屏蔽标签的href跳转功能
- HTML5绘图之Canvas标签 绘制坐标轴
- 如何在execCommand formatBlock 'p'标签中添加类或id或CSS样式?
- script标签写在哪里好?
- 从动态生成的音频标签中收听audio.ended
- 点击a标签,返回上一页
- JS实现60s倒计时(亲测有效),及span标签如何使用和禁用onclick事件
- FusionCharts的Line.swf做法,我想出现2条图形线,怎么设置graph标签