MySQL-为每个重复值获取一个计数器

Question

我有一张有两列的table。

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

我希望列“ num”显示每个重复项的增量计数器：

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

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

Answer 1

不幸的是，MySQL没有您需要的窗口功能。因此，您将必须使用以下内容：

最终查询

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 Fiddle

解释：

首先，内部选择，这会将模拟应用于row_number表中的所有记录（请参阅带有演示的SQL
Fiddle
）：

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

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

最后一个选择，返回所需的值，并将其按您要求的顺序放回去：

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