mysql重复添加计数,MySQL-为每个重复值获取一个计数器

I have a table with two columns.

+------+------+

| data | num  |

+------+------+

| a   |     |

| a   |     |

| a   |     |

| b   |     |

| b   |     |

| c   |     |

| d   |     |

| a   |     |

| b   |     |

+------+------+

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 |

+------+------+

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

UPDATE:

extended question here

解决方案

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

Final Query

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

Explanation:

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

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