SQL查找最受欢迎的类别 [英] SQL To Find Most Popular Category

问题描述

我的数据库(MySQL)中有3个表.

I have 3 tables in my DB (MySQL).

categories (name:string)
items (name:string, category_id:int)
votes (value:int, item_id:int, created_at:datetime)

所以一个类别有很多项目，而一个项目有很多票.

So a category has many items, and an item has many votes.

我想编写一个查询以获取最受欢迎的类别，这意味着获取在过去一周中项目的票数最多(上下)的类别.

I want to write a query to get the most popular categories, which means getting the categories whose items have gotten the most number of votes (both up or down) over the last week.

我开始尝试一些更简单的方法，只是获得了受欢迎的产品，但实际上我只是在猜测，这是行不通的.

I started off trying something simpler, just getting popular items, but I'm really just guessing at this point and it doesn't work.

SELECT *, COUNT(votes.item_id) AS score
FROM items
JOIN votes USING(item_id)
WHERE votes.created_at > #{1.week.ago}
ORDER BY COUNT(votes.item_id) DESC LIMIT 5;

我真的不知道我在做什么，有什么想法吗?另外，如果有人知道编写这样的更高级选择的好文章，我很乐意阅读. MySQL文档有点晦涩难懂，我不太了解'AS'和'JOINS'.

I don't really know what I'm doing, any ideas? Also, if anyone knows of a good write up on doing more advanced selects like this I'd love to read it. The MySQL documentation is a bit cryptic and I don't really understand 'AS' and 'JOINS'.

推荐答案

尝试一下.使用group by和类别名称.我已经注释掉了您指定的created at子句，如果要使用它，可以取消注释.

try this. use group by with the name of the category. i have commented out the created at clause as you specified, you can uncomment it if you want to use it.

 SELECT c.name, SUM(ABS(v.item_id)) 
 FROM categories c,items i, votes v
 WHERE c.name = i.name
    AND i.item_id=v.item_id
    --AND v.created_at > #{1.week.ago}
 GROUP BY c.name 
 ORDER BY SUM(ABS(v.item_id)) DESC LIMIT 5;

您会注意到，我没有使用JOIN关键字，而是仅使用WHERE子句过滤了查询的结果，这可能更容易理解.如果您想了解有关JOIN的更多信息，请此处是一个教程.

you will notice that i did not use the JOIN keyword but instead filtered the results of the query using only WHERE clauses, which might be easier to understand. if you want to learn more about JOINs, here is a tutorial.

这也是有关SQL别名的教程(AS子句).实际上，此站点上还有很多教程，涉及与平台无关的其他各种SQL主题.

Here, too, is a tutorial on SQL aliases (the AS clause). in fact, there are a bunch more tutorials on this site for a bunch of different SQL topics that are not platform dependent.

已根据评论进行了修复，并添加了abs功能

edit: fixed as per comments, added the abs function,

这篇关于SQL查找最受欢迎的类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！