如果是sqlserver,使用游标,循环更新
declare @bh char(10)
declare @lx char(10)
declare tj cursor for
select 读者编号 from table
open tj
fetch tj into @bh
while @@fetch_status <> -1
begin
select 读者类型=@lx from table where 读者编号=@bh
if @lx='zf'
update 编号='zf'+@bh from table where lx='zf' and 读者编号=@bh
elseif @lx='gf'
update 编号='gf'+@bh from table where lx='gf' and 读者编号=@bh
SELECT 编号,读者编号,读者类型,IDENTITY(INT,1,1) AS ID
INTO #AA
FROM YOURTABLE
ORDER BY 读者编号,读者类型
update B set
编号=A.读者类型+right('0000'+cast((select sum(1) from #AA where 读者类型=A.读者类型 and ID<=A.ID) as varchar(10)),4)
from #AA A,YOURTABLE B
WHERE A.读者编号=B.读者编号
AND A.读者类型=B.读者类型
AND A.编号=B.编号
update yourtable set
编号=读者类型+'_'+right('0000'+cast((select sum(1) from yourtable where 读者类型=a.读者类型 and 读者编号<=a.读者编号) as varchar(10)),4)
from yourtable a
--测试表
declare @表 table(编号 varchar(10),读者编号 int,读者类型 varchar(2))
insert into @表
select '1',1,'gf'
union all select 'w',2,'gf'
union all select '9',3,'gf'
union all select '8',4,'zf'
union all select '6',5,'zf'
union all select 's2',6,'zf'
--更新
update @表 set 编号=读者类型+'_'+right('0000'+cast((select sum(1) from @表 where 读者类型=a.读者类型 and 读者编号<=a.读者编号) as varchar),4)
from @表 a
Sub ChangeID()
Dim Rst As Recordset
Dim lCount As Long
Set Rst=CurrentDB.OpenRecordset("Select 读者编号,读者类型 From Table")
lCount=1
Do While Not Rst.Eof
Rst.Edit
Rst![读者编号]=Rst![读者类型] & Right("0000" & lCount,4)
Rst.Update
Rst.MoveNext
Loop
Rst.Close
End Sub