Mysql查询排名(RowNumber)和分组 [英] Mysql Query for Rank (RowNumber) and Groupings

问题描述

我在这个标题中提到了类似的问题

i have the similar problem mentioned in this thead

但它在 SQL Server 中，而 Mysql 不支持分区方式"，因为我现在知道我该怎么办?问题来了:

but it's in SQL server and Mysql doesn't support "Partition By" as i know now what can i do? Here is the Question:

我有一个包含一些列的表:用户、类别、值

I have a table that has some columns: User, Category, Value

并且我想进行一个查询，该查询将按值给出所有用户的排名，但针对类别进行重置.

And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category.

示例:

user1   CategoryA    10

user2   CategoryA    11

user3   CategoryA    9

user4   CategoryB    3

user1   CategoryB    11

查询将返回:

Rank   User     Category  
1     user2   CategoryA

2     user1   CategoryA

3     user3   CategoryA

1     user1   CategoryB

2     user4   CategoryB

有什么想法吗?

推荐答案

Edit 2:基于 OP 的评论:

Edit 2: Based on OP's comment:

它的排名有一点点错误，并且在第一个 rank 中.当具有相同 value 的类似 categories 具有顶部 rank 其中一个是 rank 中的第一个，另一个是第二个，但它们必须具有第一个 rank (1)

it worked with just a little bit wrong ranking and that's in the first rank.when similar categories with same value has the top rank one of them is the first in the rank and other is 2nd but they must have the first rank (1)

建议进行以下更改:

select rank, user, category, value
from (
  select user, @cc:=category category, @cv:=value value
    , case when @pc=@cc and @pv=@cv then @rn:=@rn
           when @pc=@cc and @pv!=@cv then @rn:=(@rn+1)
           else @rn:=1
      end as rank
    , @pc:=@cc as temp_currCat
    , @pv:=@cv as temp_currVal
  from user_category_values,
       (select @pc:='', @cc:='', 
               @pv:='', @cv:='', 
               @rn:=0) row_nums
  order by category asc, value desc
) results;

演示@MySQL 5.5.32 Fiddle

这篇关于Mysql查询排名(RowNumber)和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！