SQL查询结果中更改字段值

假如我有一个表 Goods
---------------------------------
id name
1 apple
2 apple
3 peach
4 banana
5 pear
6 orange
7 banana
8 apple
---------------------------------
现在我把重复的数据找出来，我不要删掉重复的
select * from goods where name in (select name where goods group by name having count(name)>1) order by name
找出来的结果是
---------------------------------
id name
1 apple
2 apple
8 apple
4 banana
7 banana
---------------------------------
我现在要想在找出来的名字后来加上它所在的组里的序号以区分开

---------------------------------
id name
1 apple1
2 apple2
8 apple3
4 banana1
7 banana2
---------------------------------
请问这个代码应该怎么写呢？

再执行select * from goods时，出现的结果就是
---------------------------------
id name
1 apple1
2 apple2
3 peach
4 banana1
5 pear
6 orange
7 banana2
8 apple3
---------------------------------
请下面回答问题的朋友注意一下，我的目的不是用select查询，而是更新。

应该将序号转成字符串型才能连上的：
select id, name+cast(序号 as varchar) from goods where name in (select name where goods group by name having count(name)>1) order by name