高手帮忙一下,应该不是很难的
有一个表,如下:
name         code
aa             01
bb             01
cc             02
dd             02
ee             02

现在想实现这样的结果
name         exp1         code
aa             01             01
bb             0101         01
cc             02             02
dd             0201         02
ee             0202         02

问题一解决马上结贴.

------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(name varchar(10),code varchar(10))
insert into tb(name,code) values( 'aa ', '01 ')
insert into tb(name,code) values( 'bb ', '01 ')
insert into tb(name,code) values( 'cc ', '02 ')
insert into tb(name,code) values( 'dd ', '02 ')
insert into tb(name,code) values( 'ee ', '02 ')
go

select name , code exp1 , code from
(
select px=(select count(1) from tb where code=a.code and name <a.name)+1 , * from tb a
) t where px = 1
union all
select name , exp1 = code + right( '00 ' + cast((px-1) as varchar),2),code from
(
select px=(select count(1) from tb where code=a.code and name <a.name)+1 , * from tb a
) t where px <> 1
order by exp1

drop table tb

/*
name exp1 code
---------- -------------- ----------
aa 01 01
bb 0101 01
cc 02 02
dd 0201 02
ee 0202 02

（所影响的行数为 5 行）
*/
------解决方案--------------------
drop table #i
drop table #test
create table #test (name varchar(20),code varchar(20))
insert into #test
select 'aa ', '01 ' union all
select 'bb ', '01 ' union all
select 'cc ', '02 ' union all
select 'dd ', '02 ' union all
select 'ee ', '02 '

--select * from #test

select *,id=identity(int,1,1) into #i from #test

--select * from #i


select * ,replace(code+ '0 '+(select str(count(1)) from #i where a.code=code and a.id> id ), ' ', ' ') from #i a


我只想到这样的~
------解决方案--------------------
create table tst1
(
name nvarchar(2),
code nvarchar(2)
)

insert tst1
select 'aa ', '01 ' union all
select 'bb ', '01 ' union all
select 'cc ' , '02 'union all
select 'dd ', '02 ' union all
select 'ee ' , '02 'union all
select 'ff ', '02 ' union all
select 'gg ', '02 '
go

select a.name,[exp1] = a.code + ( case(right(count(1)+99 ,2)) when '00 ' then ' ' else right(count(1)+99 ,2) end), a.code