select zmid
,convert(varchar,deh)
+(case when ID>1 then right(convert(varchar,(100+ID)),2)
else ''
end
) as 'deh'
from (
select top 100 percent *
,(select count(1) from 表 where zmid<=t.zmid) as 'ID'
from 表 t
order by zmid
)t
order by zmid
,convert(varchar,deh)
+(case when ID>1 then right(convert(varchar,(100+ID)),2)
else ''
end
) as 'deh'
from (
select top 100 percent *
,(select count(1) from 表 where zmid<=t.zmid) as 'ID'
from 表 t
order by zmid
)t
order by zmid
不对啊,上面语句执行结果
zmid deh
----------- ----------------------------------
1 01001
2 0100202
3 0100303
4 0100404
---------------------------------------------------------------
create table #t(zmid int,deh varchar(10))
insert into #t select 345 ,'02061'
insert into #t select 4898 ,'02061'
insert into #t select 10000,'02061'
select
a.zmid,
deh = a.deh + case count(b.zmid) when 1 then '' else '-'+right('00'+rtrim(count(*)),2) end
from
#t a,
#t b
where
a.deh = b.deh and a.zmid>=b.zmid
group by
a.zmid,a.dehdrop table #t/*
zmid deh
----------- ----------
345 02061
4898 02061-02
10000 02061-03
*/