mysql 组内计数,MySQL-组内计数器

I would like to add a counter for each row within a group according an ascending variable. I have a solution but it does not work if some variable within groups are equal:

CREATE TABLE tb (

g CHAR(1)

, x INTEGER

);

INSERT INTO tb (g, x)

VALUES

('a',1)

, ('a',2)

, ('a',10)

, ('b',1)

, ('b',1)

, ('b',10)

;

SELECT g,x, (SELECT COUNT(*)

FROM tb b

WHERE a.g = b.g

AND a.x > b.x

) + 1 AS counter

FROM tb a

;

What I get is:

--------------------

| g | x | counter |

--------------------

| a | 1 | 1 |

| a | 2 | 2 |

| a | 10 | 3 |

| b | 1 | 1 |

| b | 1 | 1 |

| b | 10 | 3 |

--------------------

But I would like to get:

--------------------

| g | x | counter |

--------------------

| a | 1 | 1 |

| a | 2 | 2 |

| a | 10 | 3 |

| b | 1 | 1 |

| b | 1 | 2 |

| b | 10 | 3 |

--------------------

Any idea how I can solve this problem? Thanks a lot for hints.

giordano

解决方案

User Defined Variables become handy when solving this issues. This should work:

select g, x, counter from (

select g, x,

@counter := if (g = @prev_g, @counter + 1, 1) counter,

@prev_g := g

from tb, (select @counter := 0, @prev_g := null) init

order by g, x

) s

If you don't really mind the fourth column then you could safely remove the outer select. That would improve performance a lot.