Mysql RANK 没有给出每个类别的 RANK

Question

我尝试了以下操作，但它没有对每个类别进行明智的排名。相反，在不考虑类别的情况下对所有记录进行排名。我希望每个类别重新出现排名

select  rs.Section,rs.Field1,rs.Field2 , 
@curRank := @curRank + 1 AS rank 
from (
select rs.Field1,rs2.Field2 
from table rs
inner join table2 rs2 on rs.Field1=rs2.Field1
group by rs.Section,rs.Field1 
)rs,  (SELECT @curRank := 0) r

实际输出:(来自 Mysql 输出)

Category Field1 Field2 Rank
Male     10     10     1
Male     11     10     2
Male     12     10     3
....
FeMale   10     10     11
FeMale   11     10     12
FeMale   12     10     13
....

预期输出

Category Field1 Field2 Rank
Male     10     10     1
Male     11     10     2
Male     12     10     3
....
FeMale   10     10     1
FeMale   11     10     2
FeMale   12     10     3
....

Answer 1

我假设您正在使用的查询是:

select  rs.Category, rs.Field1, rs.Field2, @curRank := @curRank + 1 AS rank 
from (select rs.Cateogry, rs.Field1, rs2.Field2 
      from table rs inner join
           table2
           rs2 on rs.Field1 = rs2.Field1
      group by rs.Category, rs.Field1, rs.Field2
     ) rs cross join
     (SELECT @curRank := 0) vars
order by cateogry, field1;

或者类似的东西，其中字段名称是一致的。您需要一个变量来指定分组。以下内容将起作用:

select  rs.Category, rs.Field1, rs.Field2,
        (@curRank := if(@c = Category, @curRank + 1,
                        if(@c := Category, 1, 1)
                       )
        ) AS rank 
from (select rs.Cateogry, rs.Field1, rs2.Field2 
      from table rs inner join
           table2
           rs2 on rs.Field1 = rs2.Field1
      group by rs.Category, rs.Field1, rs.Field2
     ) rs cross join
     (SELECT @curRank := 0, @c := NULL) vars
order by cateogry, field1;

请注意，两个变量的赋值发生在一个语句中。这很重要，因为 MySQL 不保证 select 子句中表达式的求值顺序。另请注意 group by 的显式使用。我建议您使用它，而不是依赖 group by 按特定顺序生成结果(该功能在最新版本的 MySQL 中已弃用)。