急！请高手帮助！在线等！

[lyfsurpass]

有这样的记录：
00036408
00036408
0004632
0004632
0007872
0007872
0009143
0009143
现要把它变为
00036408
00036408*
0004632
0004632*
0007872
0007872*
0009143
0009143*
请高手帮忙！

[leeboyan]

create table t(a varchar(100))
insert into t select '00036408'
insert into t select '00036408'
insert into t select '0004632'
insert into t select '0004632'
insert into t select '0007872'
insert into t select '0007872'
insert into t select '0009143'
insert into t select '0009143'
select * from t
select id=identity(int,1,1),* into temp from t
select case (id % 2)when 0 then a+'*' else a end from temp
drop table t
drop table temp
--测试--
00036408
00036408*
0004632
0004632*
0007872
0007872*
0009143
0009143*

[leeboyan]

看错了，修改，这样：
create table t(a varchar(100))
insert into t select '00036408'
insert into t select '00036408'
insert into t select '0004632'
insert into t select '0004632'
insert into t select '0007872'
insert into t select '0007872'
insert into t select '0009143'
insert into t select '0009143'
go
alter table t add id int identity(1,1)not null
go
update t set a=(case (id % 2)when 0 then a+'*' else a end)
go
alter table t drop column id
select * from t
drop table t
--测试--
a
00036408
00036408*
0004632
0004632*
0007872
0007872*
0009143
0009143*

[leeboyan]

如果有id,就不用加了

[shuichangliu]

--测试数据

if exists(select * from sysobjects where name='test' and xtype='U')
drop table test
Go
create table test(id varchar(10))
insert into test values('01')
insert into test values('01')
insert into test values('02')
insert into test values('02')

--测试语句
alter table test add iid int identity
update test set id=a.id+'*'
from test a inner join (select id,min(iid) as iid from test group by id) b
on a.id=b.id where a.iid>b.iid


--测试结果
id iid
---- ---
01 1
01* 2
02 3
02* 4

[lyfsurpass]

谢谢！！！！！！！！！！