编流水号问题

[-渔民-]

(1)
tableA

Tid Tname Tcdkey
001 b we0010009
005 a
004 c
001 d
005 e
003 f
...
...
--要求填上Tcdkey ,公式为we+tid+Tcdkey的后4位最大的加1（注：tcdkey的长度一定要为9位）

结果为:
Tid Tname Tcdkey
001 b we0010009
005 a we0050010
004 c we0040011
001 d we0010012
005 e we0050013
003 f we0030014
...
...


(2)
怎么样可以快速找出某一记录的上一条和下一条呢？（表的id号为准，是唯一的）

谢谢各位

[-渔民-]

非常感谢libin_ftsafe(子陌红尘：当libin告别ftsafe)

[子陌红尘]

declare @num varchar(4)
select @num=max(right(Tcdkey,4)) from tableA where Tcdkey is not null

update tableA
set
@num=@num+1,
Tcdkey='we'+Tid+right('000'+rtrim(@num),4)
where
Tcdkey is null

select * from tableA

[子陌红尘]

2)
declare @Tcdkey varchar(9)
set @Tcdkey = 'we0050010'

select top 1 * from tableA where right(Tcdkey,4)<right(@Tcdkey,4) order by right(Tcdkey,4) desc

select top 1 * from tableA where right(Tcdkey,4)>right(@Tcdkey,4) order by right(Tcdkey,4) asc