求一个SQL递增的算法

[Loving_18980508320]

00,01,02,03,04,05,……99,AA,AB,AC,……BA,BB,BC,……,ZA,ZB,……ZZ

根据上一个值取出下一个值，比方说，02就取03,99就取AA,BC就取BD。

谢谢了。

[發糞塗牆]

你这种混合类型的运算，我能想到的只有新建一些字典表

[LongRui888]

用rou_number排序，然后就好弄了。

[LongRui888]

有具体的数据不

[KeepSayingNo]

将这些数据按自然数顺序存储，然后找出给定值的序号，根据这序号+1找出给定值的下一个值

[霜寒月冷]

create procedure  getzm 
@n varchar(10)
as
begin
create table #t_number(number varchar(10))


create table  #t(coltest varchar(2))
insert into #t select  'A'
insert into #t select 'B'
insert into #t select'C'
insert into #t select'D'
insert into #t select 'E'
insert into #t select'F'
insert into #t  select'G'
insert into #t select'H'
insert into #t select'I'


create table  #t1(coltest varchar(2))
insert into #t1 select  'A'
insert into #t1 select 'B'
insert into #t1 select'C'
insert into #t1 select'D'
insert into #t1 select 'E'
insert into #t1 select'F'
insert into #t1  select'G'
insert into #t1 select'H'
insert into #t1 select'I'

insert into  #t_number select number from master .dbo.spt_values  where type ='p' and  number <=100
insert into  #t_number select #t1.coltest +#t.coltest from #t cross join #t1

if ISNUMERIC(@n)>0
begin
select number+1 from #t_number  where number=@n
end
else
begin
  declare @m varchar(10)
  set @m=@n
   set @n=SUBSTRING (@n ,0,2)+'%'

  create table  #temp (number varchar(10),number2 int)
  insert  into  #temp  select t.number,t .rownum  as number2 from (select number, ROW_NUMBER() over (order by  number desc ) as  rownum from #t_number where #t_number.number like  @n ) t   
  
  select number from #temp  where number2=(select number2-1  from #temp where number =@m) order by number desc
  

end

end
 go
 
exec getzm '8'
go
exec getzm 'BA'

--(无列名) --9 --number --BB 楼主记得结贴。敲好长时间。

[t101lian]

哈哈， 楼上的辛苦啦，

[t101lian]

楼主还没说， 这些数据是不是一个字段中的？

[human_2000]

写个函数把，我只写SQL了 DECLARE @A VARCHAR(2) SET @A='ab' if len(@A)=1 set @A='0'+@A SET @A=UPPER(@A) SELECT CASE WHEN ISNUMERIC(@A)=1 AND @A='99' THEN 'AA' WHEN ISNUMERIC(@A)=1 AND @A!='99' THEN CAST( CAST(@A AS INT)+1 AS VARCHAR) ELSE char(( (ASCII(SUBSTRING(@A,1,1))-65 )*26+(ASCII(SUBSTRING(@A,2,1))-65 )+1) /26+65) + char(( (ASCII(SUBSTRING(@A,1,1))-65 )*26+(ASCII(SUBSTRING(@A,2,1))-65 )+1) %26+65) END

[Loving_18980508320]

辛苦了，谢谢！