mySQL 按表中值的总和排序

Question

我有一个 mySQL 数据库表，用于存储发送给人们的消息。每条消息都可以包含点。我想知道是否有一个查询可以汇总每个人的所有积分并按排名排序？

目前我知道如何获得我正在寻找的输出的唯一方法是运行这个:

SELECT SUM(messagePoints) AS totalPoints FROM Messages

然后将结果存储在 PHP 中并按 totalPoints 对结果进行排序。我确定有一种方法可以完全通过查询来完成此操作，但我不知道从哪里开始。希望有人能给我一些建议。

消息 表示例

 messageID  |  personID  |  dateOfMessage          |  messagePoints
 ----------------------------------------------------------------
 1          |   10       |    2017-01-05 00:00:00  |     5
 2          |   10       |    2017-01-16 00:00:00  |     3
 3          |   20       |    2017-01-16 00:00:00  |     4
 4          |   10       |    2017-02-01 00:00:00  |     6
 5          |   20       |    2017-02-07 00:00:00  |     7

理想的结果:

personID |  totalPoints  |  rank
--------------------------------
10       |  14           |  1
20       |  11           |  2

如果上述输出是可能的，我想知道是否可以使用日期范围过滤器生成相同的输出，例如仅来自二月份的消息。

仅二月消息的理想结果:

personID |  totalPoints  |  rank
--------------------------------
20       |  7            | 1
10       |  6            | 2

编辑:

如果更简单，我最终需要在我的应用程序中显示 1 个人的总积分和排名(由 PHP 提供)，即仅显示 personID = 10 的结果我需要总积分和整体排名并按日期过滤。所以可能有 4 个单独的查询。

personID=10 的理想总体结果:

totalPoints 
-----------
14   

和

rank
----
1

按二月过滤的 personID=10 的理想结果:

totalPoints 
-----------
6   

和

rank
----
2

Answer 1

如果您不需要结果中的排名 - 这是一个基本的聚合查询:

select m.personID, sum(messagePoints) as totalPoints
from messages m
group by m.personID
order by totalPoints desc

将行号(排名)包含到结果中的一种方法是使用带有 AUTO_INCREMENT 列的临时表作为排名:

drop temporary table if exists tmp_messages;
create temporary table tmp_messages(
  rank int auto_increment primary key,
  personID int,
  totalPoints int
) as
    select null as rank, m.personID, sum(messagePoints) as totalPoints
    from messages m
    group by m.personID
    order by totalPoints desc;

select * from tmp_messages order by rank;

您当然可以在 WHERE 子句中包含过滤器，例如:

select m.personID, sum(messagePoints) as totalPoints
from messages m
where m.dateOfMessage >= '2017-02-01'
  and m.dateOfMessage <  '2017-03-01'
group by m.personID

你可以选择一个特定的用户:

select * from tmp_messages where personID = 10

演示:http://rextester.com/ASWF7717