MySQL result line numbering

Question

I have mysql result set as below

id        name
---------------------
1         abc
1         abc
2         xyz
2         xyz
3         pqr

Now I need to change the result set to look like this:

id        name
---------------------
1         abc-1
1         abc-2
2         xyz-1
2         xyz-2
3         pqr

To summarize: I need to number the column of names from the result set, and the numbering will only apply to rows that have more than one value in the result set.

so what will be MySQL SELECT Query ?

Answer 1

it might work, but maybe the best way I can't think of right now

set @i = 0;

select
 f.id,
 @i:=@i+1 as i,
 case 
  when c.counter <= 1 then f.name 
  else concat(f.name,'-',(@i % c.counter) +1)
 end as name
from
 foo f
join (select id, count(*) as counter from foo group by id) c on c.id = f.id
order by
 f.id,name;

EDIT: , db

Answer 2

:

• , //, . , , ,

• SQL- - , . : select t1.id, t1.name, count(t2.id) from t1, t2 where t1.id = t2.id group by t2.id (BTW, O (N ^ 2), ) .

• - . .