mysql - 根据多个类别的列总和获得排名

Question

我正在尝试获取列总和的排名，但也按类别分组。

表格是这样的:

user_id | category_id | number
   1           1        10.00
   1           1        15.00
   1           2        12.00
   1           2        10.00
   2           1        23.00
   2           1        12.50
   2           2        20.50
   2           2        19.50
   3           1        15.00
   3           1        17.50
   3           2        10.50
   3           2        19.50

我需要的是对数字列进行求和并按类别排名，并设置结果限制。

所以，我想要的是这样的结果:

 user_id | category_id | total | rank
     2          1        35.50    1
     3          1        32.50    2    
     1          1        25.00    3
     2          2        40.00    1
     3          2        30.00    2
     1          2        22.00    3 

在此示例中，我显示了前 3 名的排名。

Answer 1

您可以使用简单的GROUP BY子句来获取每个user_id、category_id的number总和:

SELECT user_id, category_id, SUM(number) AS total
FROM mytable
GROUP BY user_id, category_id

要按类别排名，您可以使用变量并将它们应用于上述查询，如下所示:

SELECT user_id, category_id, total,       
       @rn := IF (@cat = category_id,
                 IF (@cat := category_id, @rn + 1, @rn + 1),
                 IF (@cat := category_id, 1, 1)) AS rank
FROM (                 
   SELECT user_id, category_id, SUM(number) AS total
   FROM mytable
   GROUP BY user_id, category_id ) AS t
CROSS JOIN (SELECT @rn := 0, @cat := 0) AS var   
ORDER BY category_id, total DESC 

Demo here

如果您想按排名过滤结果并仅显示前 3 个排名，那么您可以将以上内容包装在子查询中并使用外部查询:

SELECT user_id, category_id, total, rank
FROM ( ... above query here ...) AS s
WHERE s.rank <= 3