34,576
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
CREATE 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 位,你可以改成 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
A04
A96
A97
A98
A99
B01
B02
B99
C01
C02
(200 行受影响)
WITH t1 AS (
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
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
你试试