SQL查询最受欢迎的类别

Question

我在我的数据库（MySQL）中有三个表格。

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

一个分类包含多个条目，一个条目有多个投票。

我想编写一个查询来获取最受欢迎的分类，也就是获取那些在过去一周内其条目获得了最多数量的投票（包括赞成和反对）的分类。

我一开始尝试了一些简单的方法，只是获取流行的条目，但现在我只是猜测，并且它并不起作用。

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”。

- Brian Armstrong

Answer 1

试试这个。使用类别名称进行分组。我已经注释掉了创建时间子句，因为你指定了，如果你想使用它，可以取消注释。

 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的内容，请参阅这个教程。

在这里，也有一个关于SQL别名的教程（AS子句）。实际上，在这个网站上还有许多其他与不依赖平台的不同SQL主题相关的教程。 编辑：按照评论的要求进行修正，添加了ABS函数。

- akf

Answer 2

SELECT c.name, sum(v.value) as cnt
 FROM categories c
 JOIN items i ON i.category_id = c.id
 JOIN votes v ON v.item_id = i.id
 WHERE v.created_at > #{1.week.ago}
 GROUP BY c.name 
 ORDER BY cnt DESC LIMIT 5;

编辑：安德鲁说得很好，我已经修复了查询

- Hardwareguy

---

这并没有考虑到一票的价值可能大于一个或者是负值的情况。 - Andrew Siemer

Answer 3

这是在SQL Server中...但我认为对于一个MySQL的人来说很容易转换！

select top 5
    c.name as CategoryName, sum(v.value) as VoteSum
from categories c inner join items i
    on c.category_id = i.category_id
    inner join votes v
        on i.item_id = v.item_id
where created_at between dateadd(week,-1,getdate()) and getdate()
group by c.name
order by sum(v.value) desc

选择前5个--要在计算中显示的类别数量

c.name as CategoryName, sum(v.value) as VoteSum --获取类别名称和投票总数

从categories c内部连接items i--连接层次结构：类别到项目

on c.category_id = i.category_id --按category_id

内部连接votes v--项目到投票

on i.item_id = v.item_id --按item_id

where created_at between dateadd(week,-1,getdate()) and getdate() --指定要包括的日期范围

group by c.name --按类别分组结果

order by sum(v.value) desc --按总和值排序结果

这里是一些SQL（来自SQL Server）来启动和运行表：

CREATE TABLE [categories](
    [category_id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL
)

CREATE TABLE [items](
[item_id] [int] IDENTITY(1,1) NOT NULL,
[category_id] [int] NOT NULL,
[name] [varchar](50)
)

CREATE TABLE [dbo].[votes](
[vote_id] [int] IDENTITY(1,1) NOT NULL,
[value] [int] NOT NULL,
[item_id] [int] NOT NULL,
[created_at] [datetime] NOT NULL
)

insert into categories (name) values (' asp.net ')
insert into categories (name) values (' c#  ')
insert into categories (name) values (' vb  ')
insert into categories (name) values (' sql ')
insert into categories (name) values (' html    ')
insert into categories (name) values (' javascript  ')

insert into items (category_id, name) values (  1   ,'  session handling    ')
insert into items (category_id, name) values (  1   ,'  mvc vs mvp  ')
insert into items (category_id, name) values (  1   ,'  code behind or no code behind   ')
insert into items (category_id, name) values (  2   ,'  LINQ?   ')
insert into items (category_id, name) values (  2   ,'  lamdas  ')
insert into items (category_id, name) values (  2   ,'  multi-threaded code ')
insert into items (category_id, name) values (  2   ,'  SOLID principles    ')
insert into items (category_id, name) values (  3   ,'  vb vs C#    ')
insert into items (category_id, name) values (  3   ,'  VB.NET over vb6 ')
insert into items (category_id, name) values (  4   ,'  CLR procedures or stored procedures ')
insert into items (category_id, name) values (  4   ,'  ORMs vs stored procedures and views ')
insert into items (category_id, name) values (  6   ,'  jquery instead of standard DOM  ')

insert into votes (value, item_id, created_at) values ( -1  ,   1   ,'  6/26/2009 18:59 ')
insert into votes (value, item_id, created_at) values ( 1   ,   1   ,'  6/26/2009 18:59 ')
insert into votes (value, item_id, created_at) values ( 3   ,   1   ,'  6/26/2009 18:59 ')
insert into votes (value, item_id, created_at) values ( 3   ,   1   ,'  6/26/2009 18:59 ')
insert into votes (value, item_id, created_at) values ( 2   ,   2   ,'  6/26/2009 18:59 ')
insert into votes (value, item_id, created_at) values ( 2   ,   2   ,'  6/26/2009 18:59 ')
insert into votes (value, item_id, created_at) values ( 4   ,   2   ,'  6/26/2009 18:59 ')
insert into votes (value, item_id, created_at) values ( -3  ,   2   ,'  6/26/2009 18:59 ')
insert into votes (value, item_id, created_at) values ( 2   ,   4   ,'  6/26/2009 19:00 ')
insert into votes (value, item_id, created_at) values ( 6   ,   4   ,'  6/26/2009 19:00 ')
insert into votes (value, item_id, created_at) values ( 3   ,   4   ,'  6/26/2009 19:00 ')
insert into votes (value, item_id, created_at) values ( 5   ,   4   ,'  6/26/2009 19:00 ')
insert into votes (value, item_id, created_at) values ( 8   ,   7   ,'  6/26/2009 19:00 ')
insert into votes (value, item_id, created_at) values ( 3   ,   6   ,'  6/26/2009 19:00 ')
insert into votes (value, item_id, created_at) values ( 8   ,   7   ,'  6/26/2009 19:01 ')
insert into votes (value, item_id, created_at) values ( 2   ,   5   ,'  6/26/2009 19:01 ')

- Andrew Siemer

---

他指定了MySQL，TOP 5不是SQL Server的构造吗？ - Hardwareguy

是的，顶部是MSSQL。只需去掉前5个并在底部添加限制为5个。 - corymathews

Answer 4

你可以在这种情况下使用 group by 并删除 join。但我总是在使用 group by 时搞砸，不过大致如下：

SELECT COUNT(votes.item_ID) AS score, 
  (SELECT ItemTitle FROM items WHERE items.item_id = votes.item_id) as Title
FROM votes
WHERE votes.created_at > #{1.week.ago}
Group By Title
Order By score
Limit 5

AS

"as" 允许您给某个东西命名。

请注意上面的 as score，它给出了 count(votes.item_id) 的结果，因为它之前没有列名。如果您想在查询的其余部分中用另一个名称调用某些内容，也可以使用此功能。

如果您去掉 as score，它将返回一个没有标题且无法通过名称访问的列，只能通过数字访问。

JOIN

连接将合并 2 个表作为 1 个临时表，并返回此表。有内部、外部、左侧、右侧和交叉连接。每种连接都有自己的优点，但都存在相同的问题，即速度较慢。请查看子查询以替换大多数连接。

您还需要避免使用 Select *，而是列出所有您需要的内容。

找出这些内容的最佳方法是运行它们并查看它们返回什么以及它们应该做什么 w3Schools Joins。

- corymathews

---

计数应该改为求和，正如@Paul Chernoch所指出的那样，使用绝对值可能是值得的。 - akf

Answer 5

1) 我假设类别表也有一个category_id字段，而items表有一个item_id字段，否则您将无法链接这些表。 2) AS关键字通常是可选的。

SELECT c.name, SUM(ABS(v.value))
FROM categories c
INNER JOIN items i ON c.category_id = i.category_id
INNER JOIN votes v ON v.item_id = i.item_id
WHERE v.created_at BETWEEN DATE_SUB(NOW(), INTERVAL -7  DAYS) AND NOW()
GROUP BY c.name
ORDER BY 2 DESC
LIMIT 10

• AS关键字用于创建表名的同义词。大多数情况下，这只是一个简写，但如果您执行自连接，其中表与自身连接，则需要区分它们。如果两个表具有相同的字段名称，则需要指定使用哪个表的字段，因此c.category_id来自“c”表，这意味着类别表。
• 连接是必不可少的。开始阅读吧。
• 在我的解决方案中，我使用了DATE_SUB，它是mySQL本地的。我不知道还有多少其他数据库使用该函数，但它们都有类似的东西。
• 我的查询为您提供了前十个最受欢迎的类别，最受欢迎的排在第一位。请注意，在mySQL中，LIMIT N子句是如何实现的。在SQLServer中，在SELECT关键字之后使用TOP N。在Oracle中，它是以另一种方式完成的。
• 我取了投票的绝对值，因为您说要包括赞成或反对的投票。
• ORDER BY 2 DESC表示按选择中的第二列降序排序。您可以在此处使用实际表达式，但那样会更麻烦。
• GROUP BY是必需的。如果使用了任何聚合函数，则不是常量或聚合函数SUM、COUNT、MAX等的每个列都必须出现在GROUP BY子句中。

- Paul Chernoch

---

关于Oracle中的TOP/LIMIT等效项：它是ROWNUM，如ROWNUM < 10 - akf

Answer 6

这是我最终采用的方案（@akf答案的稍作修改版本）。其他答案也很好，但对于像我这样的新手来说，这个答案似乎是最简单的。我发现它不使用任何连接语句？我从来没有想过这是可能的。我喜欢它的简洁和简单。

SELECT c.*, SUM(ABS(v.vote)) AS score
FROM categories c,items i, votes v
  WHERE c.id = i.category_id
  AND i.id = v.item_id
  AND v.created_at > '#{1.week.ago}'
GROUP BY c.id
ORDER BY score DESC LIMIT 5;

（我忘了在问题中提到每个表都有一个 'id' 列，你可以在这里看到它的使用。）

在这种情况下，对投票值的绝对值求和是有效的，因为正负数都存在，正如一些帖子中正确指出的那样。但是，我觉得更简单的方法是只计算数据库中投票记录的数量。我尝试过 "COUNT(v) AS score"，但这并不起作用。如果有人知道怎么做，请发表评论。谢谢大家！

- Brian Armstrong

---

我认为这个查询仍然被视为一个JOIN查询，但是使用了快捷方式。 - Zubair1