MySQL-为每个重复值获取一个计数器 [英] MySQL - Get a counter for each duplicate value

问题描述

我有一个两列的表.

+------+------+
| data | num  | 
+------+------+
| a    |      | 
| a    |      |
| a    |      |
| b    |      |
| b    |      |
| c    |      |
| d    |      |
| a    |      |
| b    |      | 
+------+------+

我希望列"num"显示每个重复项的增量计数器:

I want the column "num" displays an incremental counter for each duplicate entry:

+------+------+
| data | num  | 
+------+------+
| a    |    1 | 
| a    |    2 |
| a    |    3 |
| b    |    1 |
| b    |    2 |
| c    |    1 |
| d    |    1 |
| a    |    4 |
| b    |    3 | 
+------+------+

除了mySQL查询外，是否可以在没有任何其他脚本的情况下完成此操作?

Is this possible to be done without any other scripting besides a mySQL query?

扩展问题此处

推荐答案

不幸的是，MySQL没有所需的窗口函数.因此，您将必须使用以下内容:

Unfortunately, MySQL does not have windowing functions which is what you will need. So you will have to use something like this:

最终查询

select data, group_row_number, overall_row_num
from
(
  select data,
        @num := if(@data = `data`, @num + 1, 1) as group_row_number,
        @data := `data` as dummy, overall_row_num
  from
  (
    select data, @rn:=@rn+1 overall_row_num
    from yourtable, (SELECT @rn:=0) r
  ) x
  order by data, overall_row_num
) x
order by overall_row_num

请参见带有演示的SQL小提琴

说明:

首先，内部选择，这会将模拟row_number应用于表中的所有记录(请参见

First, inner select, this applies a mock row_number to all of the records in your table (See SQL Fiddle with Demo):

select data, @rn:=@rn+1 overall_row_num
from yourtable, (SELECT @rn:=0) r

查询的第二部分，将表中的每一行与下一行进行比较，以查看其是否具有相同的值，如果没有，则重新开始group_row_number(请参见

Second part of the query, compares each row in your table to the next one to see if it has the same value, if it doesn't then start the group_row_number over (see SQL Fiddle with Demo):

select data,
      @num := if(@data = `data`, @num + 1, 1) as group_row_number,
      @data := `data` as dummy, overall_row_num
from
(
  select data, @rn:=@rn+1 overall_row_num
  from yourtable, (SELECT @rn:=0) r
) x
order by data, overall_row_num

最后一个选择，返回您想要的值，并将其放回您请求的顺序中:

The last select, returns the values you want and places them back in the order you requested:

select data, group_row_number, overall_row_num
from
(
  select data,
        @num := if(@data = `data`, @num + 1, 1) as group_row_number,
        @data := `data` as dummy, overall_row_num
  from
  (
    select data, @rn:=@rn+1 overall_row_num
    from yourtable, (SELECT @rn:=0) r
  ) x
  order by data, overall_row_num
) x
order by overall_row_num

这篇关于MySQL-为每个重复值获取一个计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！