SQL 查询查找趋势

发布于 2024-10-28 06:05:04 字数 437 浏览 2 评论 0原文

我有两个表 - 投票和污点。投票有名称、时间和喜欢/不喜欢参数作为表列。印记只是人们投票选出的一些标签。他们有一个分数（喜欢减去不喜欢）和 number_of_votes 作为字段。

我整理了一个查询来查找趋势污点 -

SELECT name, number_of_votes, score FROM `vote`
INNER JOIN `blote` ON vote.name=blote.name
WHERE UNIX_TIMESTAMP(now()) - `time` < 60*60*24*7
GROUP BY vote.name
ORDER BY blote.number_of_votes DESC
LIMIT 25

这显然是错误的。它会找到那些在上周拥有更多选票且至少拥有一票的 Blotes。我想要的是找到上周获得更多选票的人。希望这是有道理的。谢谢。

原文

I have two tables - votes and blotes.
Votes have a name, time and like/dislike parameter as table columns. Blotes are just some tags people voted for. They have a score (likes minus dislikes) and number_of_votes as fields.

I put together a query to find trending blotes -

SELECT name, number_of_votes, score FROM `vote`
INNER JOIN `blote` ON vote.name=blote.name
WHERE UNIX_TIMESTAMP(now()) - `time` < 60*60*24*7
GROUP BY vote.name
ORDER BY blote.number_of_votes DESC
LIMIT 25

Which is apparently wrong. It finds those Blotes which have more votes and at least one vote in last week. What I want is to find those which have more votes in last week. Hope it makes sense. Thank you.

分享到QQ

分享到微博

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

发布评论

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

羁拥 2024-11-04 06:05:04

有时写一个问题很有帮助。我知道我需要使用 count()，提交问题后不久，我就知道了如何做到这一点。

这是：

SELECT count(time), blote.name, number_of_votes, score  FROM `vote`
INNER JOIN `blote` ON vote.name=blote.name
WHERE UNIX_TIMESTAMP(now()) - `time` < 60*60*24*7
GROUP BY vote.name
ORDER BY count(time) DESC
LIMIT 25

有什么方法可以改进这个查询吗？

Sometimes it helps a lot to write a question. I knew I need to use count(), and few moments after submitting a question, I saw how to do it.

Here it is:

SELECT count(time), blote.name, number_of_votes, score  FROM `vote`
INNER JOIN `blote` ON vote.name=blote.name
WHERE UNIX_TIMESTAMP(now()) - `time` < 60*60*24*7
GROUP BY vote.name
ORDER BY count(time) DESC
LIMIT 25

Any way to improve this query?

回复收藏 0 原文

~没有更多了~