insert into dd(id) select (max(id)+1) as id from dd ??
DECLARE @test TABLE(id VARCHAR(10))INSERT INTO @test(id) SELECT 'ASD001'INSERT INTO @test(id) SELECT STUFF('ASD000',7-LEN(ISNULL(MAX(STUFF(id,1,3,'')),0)+1),LEN(ISNULL(MAX(STUFF(id,1,3,'')),0)+1),ISNULL(MAX(STUFF(id,1,3,'')),0)+1) FROM @testSELECT * FROM @test
USE TEMPDB GO IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB(COL1 VARCHAR(10)) INSERT INTO TB SELECT 'ASD001' GOINSERT INTO TB SELECT 'ASD'+RIGHT('000'+LTRIM(MAX(CONVERT(INT,RIGHT(COL1,3)))+1),3) FROM TBSELECT * FROM TB /* ASD001 ASD002 */
CREATE TABLE #test( InternalID int IDENTITY(1,1) NOT NULL CHECK(InternalID BETWEEN 1 AND 999), ID AS CAST(N'ASD'+RIGHT(N'000'+CAST(InternalID AS nvarchar(10)),3) AS nvarchar(10)) ) GO INSERT INTO #test DEFAULT VALUES INSERT INTO #test DEFAULT VALUES INSERT INTO #test DEFAULT VALUES GO SELECT * FROM #test
select left(max(id),3)+right(cast(1000+cast(right(max(id),3) as int)+1) as nvarchar(10),3) from tb
---
隐式转为int
SELECT 'ASD001'INSERT INTO @test(id)
SELECT STUFF('ASD000',7-LEN(ISNULL(MAX(STUFF(id,1,3,'')),0)+1),LEN(ISNULL(MAX(STUFF(id,1,3,'')),0)+1),ISNULL(MAX(STUFF(id,1,3,'')),0)+1) FROM @testSELECT * FROM @test
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL1 VARCHAR(10))
INSERT INTO TB
SELECT 'ASD001'
GOINSERT INTO TB
SELECT 'ASD'+RIGHT('000'+LTRIM(MAX(CONVERT(INT,RIGHT(COL1,3)))+1),3) FROM TBSELECT * FROM TB
/*
ASD001
ASD002
*/
CREATE TABLE #test(
InternalID int IDENTITY(1,1) NOT NULL CHECK(InternalID BETWEEN 1 AND 999),
ID AS CAST(N'ASD'+RIGHT(N'000'+CAST(InternalID AS nvarchar(10)),3) AS nvarchar(10))
)
GO
INSERT INTO #test DEFAULT VALUES
INSERT INTO #test DEFAULT VALUES
INSERT INTO #test DEFAULT VALUES
GO
SELECT * FROM #test