SQL查找最受欢迎的类别

本文介绍了SQL查找最受欢迎的类别的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的数据库(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,