select case (select count(*) from 原表 where 名称=a.名称 and 编号<=a.编号) when 1 then 名称 else right('00'+cast((select count(*) from 原表 where 名称=a.名称 and 编号<=a.编号)-1 as varchar),2) end as 名称,
编号,内容
from 原表 a
order by a.名称+'',编号
编号,内容
from 原表 a
order by a.名称+'',编号
create table t ([name] varchar(10),num varchar(10),other int)
insert into t select 'keting','0012',1
insert into t select 'keting','0013',1
insert into t select 'keting','0014',1
insert into t select 'keting','0016',1
insert into t select 'canting','00024',1
insert into t select 'canting','0035',1
insert into t select 'canting','0036',1select case when id=0 then TT.[name] else right('1000'+convert(varchar,[id]),2) end as [name] ,TT.num,TT.other from
(select [id]=(select count(*) from t a where a.[name]=t.[name] and a.num<t.num),[name],num,other from t) TTdrop table tname num other
---------- ---------- -----------
keting 0012 1
01 0013 1
02 0014 1
03 0016 1
canting 00024 1
01 0035 1
02 0036 1