34,590
社区成员
发帖
与我相关
我的任务
分享
declare @t table(v varchar(1000));
insert into @t select 'ABCD201112345678A00001' union all
select 'ABCD201112345678A00003' union all
select 'ABCD201112345678A00004' union all
select 'ABCD201112345678A00005' union all
select 'ABCD201112345678B00001' union all
select 'ABCD201112345678B00002' union all
select 'ABCD201112345678B00003' union all
select 'ABCD201112345678B00004' union all
select 'ABCD201112345678B00005' union all
select 'ABCD201112345678C00001' ;
;with cte as (
select *,rn=ROW_NUMBER() over( partition by c2 order by c3) from (select LEFT(v,16) as c1,SUBSTRING(v,17,1) as c2,RIGHT(v,5) as c3 from @t group by LEFT(v,16),SUBSTRING(v,17,1),RIGHT(v,5)
) x
)
select top 1 * from cte order by cast(c3 as int)-rn desc,c2 desc
-- 这个号码的前一个号为缺的最大号,这里只用了5个数据测试
/*
c1 c2 c3 rn
-------------------------------- ---- ---------- --------------------
ABCD201112345678 A 00003 2
*/
declare @str varchar(30)='ABCD201112345678B99999'
select left(@str,16)+char(ASCII(substring(@str,17,1))+1)+'00001'
/*
----------------------
ABCD201112345678C00001
select top 1 字段 from tb where substring(字段,5,4)=year(getdate())
order by substring(字段,17,1) desc,right(字段,5)*1 desc
select top 1 字段 from substring(字段,5,4)=year(getdate())
order by substring(字段,17,1) desc,right(字段,5)*1 desc