枚举mysql中基于组的行 [英] Enumerate rows in mysql based on groups

问题描述

例如，我有一组记录

A   B
1   5
1   6
1   9
2   1
2   8

我们有两个组(A = 1和A = 2)，内部组记录按B中的值排序.

where we have two groups (A=1 and A=2) and inside groups record are ordered by values in B.

我需要在每个组中添加一个带有枚举的列

And I need to add a column with enumeration inside each group

A   B   C
1   5   1
1   6   2
1   9   3
2   1   1
2   8   2

在给定相同的组ID(A)的情况下，我尝试使用嵌套查询来获取许多记录中B值小于当前记录的记录，但是它太慢了. 我在代码的其他部分使用了一些行枚举(使用select @rownum:= @ rownum + 1 num)，但我在此枚举了所有记录.所以我很感兴趣，是否有可能实现它的iside查询(如果可能的话，我想避免游标循环等). 谢谢！

I tried to use nested query to fetch a number of records that have a value in B less than for current record given the same group id (A) but it is too slow. I use some enumeration of rows in other part of code (using select @rownum:=@rownum+1 num) but I enumerate all records there. So I am interested, is it possible to implement it iside query (if possible I'd like to avoid cursor loops etc). Thanks!

推荐答案

最有效的方法是使用变量:

The most efficient way to do this is using variables:

select t.a, t.b,
       (@rn := if(t.a = @a, @rn + 1,
                  if(t.a := @a, 1, 1)
                 )
       ) as c
from table t cross join
     (select @a := '', @rn := 0) params
order by t.a, t.b;

另一种方法使用相关的子查询或联接，但假定每个a的b值都是唯一的(或表中有唯一的列):

An alternative approach uses a correlated subquery or join but assumes that the b values are unique for each a (or there is a unique column in the table):

select t.a, t.b,
       (select count(*) from table t2 where t2.a >= t.a) as c
from table t;

这篇关于枚举mysql中基于组的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！