id 字段 char(20)
---------------------
PG20060731-1
PG20060731-2
PG20060731-3
PG20060731-4
PG20060731-5
PG20060731-6
PG20060731-7
PG20060731-8
PG20060731-9
PG20060731-10
PG20060731-11
............PG20060731-n----------------------
字段格式是这样'PG'+'当天日期'+'-'+'递增数'我想得到记录的最大编号,例如PG20060731-11该怎样写SQL?
---------------------
PG20060731-1
PG20060731-2
PG20060731-3
PG20060731-4
PG20060731-5
PG20060731-6
PG20060731-7
PG20060731-8
PG20060731-9
PG20060731-10
PG20060731-11
............PG20060731-n----------------------
字段格式是这样'PG'+'当天日期'+'-'+'递增数'我想得到记录的最大编号,例如PG20060731-11该怎样写SQL?
insert into @t select'PG20060731-2'
insert into @t select'PG20060731-3'insert into @t select'PG20060731-4'insert into @t select'PG20060731-5'insert into @t select'PG20060731-6'insert into @t select'PG20060731-7'insert into @t select'PG20060731-8'insert into @t select'PG20060731-9'insert into @t select'PG20060731-10'insert into @t select'PG20060731-11'
select max(id) from @t最大取到9
insert into @t select 'PG20060731-2'
insert into @t select 'PG20060731-3'insert into @t select 'PG20060731-4'insert into @t select 'PG20060731-5'insert into @t select 'PG20060731-6'insert into @t select 'PG20060731-7'insert into @t select 'PG20060731-8'insert into @t select 'PG20060731-9'insert into @t select 'PG20060731-10'insert into @t select 'PG20060731-11'/**/select max(convert(int,right(rtrim(id),len(id)-11))) from @t
-----------
11
set @aa = 0
select @aa = max(cast(right(id,len(ltrim(rtrim(id)))-len(left(ltrim(rtrim(id)),11)))) as integer)) from tablename
select id from tablename where cast(right(id,len(ltrim(rtrim(id)))-len(left(ltrim(rtrim(id)),11)))) as integer) =@aa