SELECT CASE WHEN RIGHT(samplecode,4)<9999
THEN LEFT(samplecode,1)+RIGHT('000'+CAST(RIGHT(samplecode,4)+1 AS VARCHAR(10)),4)
ELSE CHAR(ASCII(LEFT(samplecode,1))+1)+'0001'END
FROM t你试试
THEN LEFT(samplecode,1)+RIGHT('000'+CAST(RIGHT(samplecode,4)+1 AS VARCHAR(10)),4)
ELSE CHAR(ASCII(LEFT(samplecode,1))+1)+'0001'END
FROM t你试试
SELECT ISNULL(MAX(samplecode),'A0000') lastcode
FROM t
)
,t2 AS (
SELECT LEFT(lastcode,1) prefix,
CONVERT(int,RIGHT(lastcode,4)) number
FROM t1
)
SELECT CASE WHEN number = 9999 THEN
CHAR(ASCII(prefix)+1) + '0001'
ELSE
prefix + RIGHT('0000'+Convert(varchar(4),number+1),4)
END newcode
FROM t2
注:Z9999用完后怎么办?没有处理。
-- 我这里用了 2 位,你可以改成 4 位的。
-- 数据太多了,我删除了中间的,应该符合你的要求。
drop table x
go
create table x(id varchar(10))
go
with a as (
select ISNULL(max(id),'A00') maxid from x)
insert into x (id)
select
case when substring(maxid,2,30) = '99' then CHAR(ascii(left(maxid,1)) +1) + '01'
else LEFT(maxid,1) + right('0000' + cast((SUBSTRING(maxid,2,30) + 1 ) as varchar(30)) ,2)
end
from a
go 200
select * from x
go批处理执行已完成 200 次。
id
----------
A01
A02
A03
A04A96
A97
A98
A99
B01
B02B99
C01
C02(200 行受影响)
GOCREATE TABLE #Tmp1
(
ID INT IDENTITY(1,1),
samplecode VARCHAR(10)
)INSERT INTO #Tmp1
( samplecode )
VALUES ( 'A001')DECLARE @CurrentCode VARCHAR(10)='A001'
SELECT @CurrentCode=MAX(samplecode)
FROM #Tmp1
SELECT @CurrentCode= CASE WHEN RIGHT(@CurrentCode,3)='999' THEN CHAR(ASCII(LEFT(@CurrentCode,1))+1)+'0001'
ELSE LEFT(@CurrentCode,1) + RIGHT(REPLICATE('0',3) + RTRIM(CAST(STUFF(@CurrentCode,1,1,'') AS INT)+1),3) END
SELECT @CurrentCode
2、建议后四位从"0000"~"9999",这样比较好统计;
3、新增个自增列,每次插入数据的时候将samplecode置为空值,然后再根据自增列去update一下;
4、由于自增列可以自动生成数据,你只要判断自增列(比如列名为ID)对10000的除数和模,就可以得出samplecode
UPDATE TB SET samplecode=
CHAR(@i/10000+64)+CASE WHEN @i%10000 <=9 THEN '000'+CAST(@i%10000 AS varchar)
WHEN @i%10000 >=10 AND @i%10000 <=99 THEN '00'+CAST(@i%10000 AS varchar)
WHEN @i%10000 >=100 AND @i%10000 <=999 THEN '0'+CAST(@i%10000 AS varchar)
WHEN @i%10000 >=1000 AND @i%10000 <=9999 THEN CAST(@i%10000 AS varchar)
END
where samplecode is null)