select 'abc'+right('00'+ltrim(max(right([type_id],3))+1),3) from tb where person_id=1000

------解决方案--------------------
SQL code
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,TYPE_ID varchar(7),Person_ID int)
insert into #T
select 1,'abc_001',1000 union all
select 2,'abc_002',1001 union all
select 3,'abc_001',1000 union all
select 4,'abc_002',1001 union all
select 5,'abc_003',1000

select 'abc_'+right('00'+ltrim(max(right([type_id],3))+1),3) from #T where person_id=1000
/*
abc_004
*/