如何在MySQL中按块编号数据？

I'd like to number chunks of filesets continuously, say from 1 to 3 for example.

我想连续编号文件集的数量,例如从1到3。

Let's say I have the following:

假设我有以下内容:

ID | random_nr | ordering
.. | .. | ..
19 | 12 | 2
19 | 21 | 3
20 | 13 | 1*
20 | 14 | 2*
20 | 22 | 3*
21 | 15 | 1
21 | 17 | 2
.. | .. |

My code so far only numbers all sets right through:

到目前为止,我的代码只对所有集合进行了编号:

SET @c = 0; 
SELECT
`ID`,
`random`,
(@c := @c + 1) AS "ordering"
FROM `tabelle` WHERE ...

How could I change it so I get the result mentioned above? Thanks a million!!

我怎么能改变它以便得到上面提到的结果?太感谢了!!

2 个解决方案

#1

Here's one way...

这是一种方式......

 SELECT x.*
      , COUNT(*) ordering 
   FROM tabelle x 
   JOIN tabelle y 
     ON y.id = x.id 
    AND y.random_nr <= x.random_nr 
  GROUP 
     BY x.id
      , x.random_nr;

Another way is to add @curr and @prev markers to your existing query.

另一种方法是将@curr和@prev标记添加到现有查询中。

#2

SET @i = null;
SELECT
`ID`,
`random`,
(@c := IF(ID = @i, @c + 1, 1) + (@i:=ID)-ID AS "ordering"
FROM `tabelle` WHERE ...
ORDER BY ID

So each time the ID is not equal to current value of @i, then @c starts over at 1.

所以每当ID不等于@i的当前值时,@ c从1开始。

#1