需要帮助构造SQL SELECT语句以获得“top 10 x”

I have this Points table:

我有这个点表:

===========================================
FIELD           :      TYPE
===========================================
id                 int(11)
user_id            int(11)
value              int(11)
type               enum('add','subtract')
timestamp          int(11)
===========================================

Basically this table stores records of user points.

基本上这个表存储用户点的记录。

Now my question is: How do I get the top 10 users with the highest amount of total points?

现在我的问题是:如何获得总积分最高的10个用户?

Note: I have been told to change my schema -- drop the 'type' field and put positive and negative values for the 'value' field. I just want to know if I could achieve the desired query using the current schema.

注意:我被告知要改变我的模式——删除“type”字段，并为“value”字段添加正和负值。我只是想知道是否可以使用当前模式实现所需的查询。

Thanks in advance

谢谢提前

1 个解决方案

#1

I would try something like this:

我想试试这样的东西:

SELECT
  user_id,
  SUM( IF(`type`='add',+1,-1) * `value`) AS user_points
FROM Points
GROUP BY user_id
ORDER BY SUM( IF(`type`='add',+1,-1) * `value`) DESC
LIMIT 10

Seems to work: http://sqlfiddle.com/#!2/dd207/1

似乎工作:http://sqlfiddle.com/ ! 2 / dd207/1

Alternative, maybe faster, maybe not, the trick is moving out the SUM(IF()) part from the inner query:

可选的，可能更快，也可能不会，诀窍是将SUM(IF()部分从内部查询中移出:

http://sqlfiddle.com/#!2/dd207/9

http://sqlfiddle.com/ ! 2 / dd207/9

SELECT
  user_id,
  SUM( IF(`type`='add',+1,-1) * `valuesum`)
FROM (
      SELECT
        user_id,
        `type`,
        SUM(`value`) AS `valuesum`
      FROM     Points
      GROUP BY user_id, `type`
     ) AS p
GROUP BY p.user_id
ORDER BY SUM( IF(`type`='add',+1,-1) * `valuesum`) DESC
LIMIT 10

Probably both solution have to do a full table scan to summarize everything, so this situation might be a good candidate for some wise denormalization (collecting sums per users in another table, for example).

可能这两种解决方案都必须进行完整的表扫描来总结所有内容，因此这种情况可能是某种明智的非规范化(例如，在另一个表中收集每个用户的总数)的一个很好的候选者。

#1