在列中的每个副本上递增计数器

I have a table:

我有一个表:

id | value
1  | -
1  | a
2  | b
1  | c
3  | d
2  | e

then I need a counter column which begins from 1 for every different value in the id column

然后我需要一个计数器列，它从1开始，针对id列中的每个不同值

Desired select result:

想要选择结果:

id | value | counter
1  | -     | 1
1  | a     | 2
2  | b     | 1
1  | c     | 3
3  | d     | 1
2  | e     | 2

All I found was for counting the number of duplicates etc., but not for an incrementing counter on each duplicate of a specific column...?

我所发现的只是计算重复的次数等等，而不是对特定列的每一个重复的递增计数器…?

4 个解决方案

#1

If you do not care about ordering, only about corresponding row number, use variables like this:

如果你不关心排序，只关心对应的行号，使用如下变量:

SELECT 
  t.*, 
  @i:=IF(id=@id, @i+1, 1) AS num, 
  @id:=id 
FROM 
  t 
  CROSS JOIN (SELECT @i:=0, @id:=0) AS init 
ORDER BY id

#2

What you are trying to accomplish is called RANKING . Unfortunately MySQL doesnot have ranking functions like in SQL Server ROW_NUMBER() , DENSE_RANK() etc..

你想要达到的目标叫做排名。不幸的是，MySQL没有像SQL Server ROW_NUMBER()、DENSE_RANK()这样的排序函数。

You can try below query for MySQL

你可以试试下面的MySQL查询。

SELECT t.ID, t.Value, count(*) as Counter 
FROM tableName t
JOIN tableName b ON t.ID = b.ID AND t.Value >= b.Value
GROUP BY t.ID, t.Value

SQL Fiddle DEMO

SQL小提琴演示

#3

select  t.id1,  t.value,
row_number()  over (partition by t.id1 order by t.value ) counter
from test t;

I hope this will be useful:)

我希望这将是有用的:

#4

This will do the work for you:

这对你有帮助:

SELECT [id]
      ,[value]
      ,ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY value) AS 'counter'
  FROM [TableName]

ROW_NUMBER will add the numbering. The PARTITION BY clause allows us to group the results within the call to ROW_NUMBER() without grouping them ourselves via a GROUP BY. It just tells the ROW_NUMBERR what groupings to use when it does its counting.

ROW_NUMBER将添加编号。通过PARTITION BY子句，我们可以在对ROW_NUMBER()的调用中对结果进行分组，而不必自己通过group BY对它们进行分组。它只告诉ROW_NUMBERR在计数时使用什么组。

For MySql you can use those links:

对于MySql，您可以使用以下链接:

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

http://www.folkstalk.com/2013/03/grouped-row-number-function-mysql.html

In your case:

在你的例子:

SET @row_number:=0;
SET @id:='';
SELECT @row_number:=CASE WHEN @id=id THEN @row_number+1 ELSE 1 END AS row_number , @id:=id AS id, value
FROM TableName
ORDER BY id;

#1