MSSQL的自动编号，如何指定特定的类型呀？

[q85893349]

我想使用自动编号，但是不知道如何使用指定的类型作为自动编号呀。
比如说我想我的自动编号是：
A001
A002
A003
这样应该如何操作呀，高手指点。

[bancxc]

需要自己写代码完成自动编号
触发器 函数 存储过程 代码

[--小F--]

--参考:
--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
	RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

--插入资料
BEGIN TRAN
	INSERT tb(col) VALUES(1)
	INSERT tb(col) VALUES(2)
	INSERT tb(col) VALUES(3)
	DELETE tb WHERE col=3
	INSERT tb(col) VALUES(4)
	INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN

--显示结果
SELECT * FROM tb
/*--结果
BH         col 
---------------- ----------- 
BH000001  1
BH000002  2
BH000003  4
BH000004  14
--*/

[jack_ailly]

create table tb
(id int identity,
name varchar(10),
co
code as 'BH'+right('0000'+cast(id as varchar),5))
go
insert tb(name) select 'A'
union all select 'B'
union all select 'C'
union all select 'D'

select * from tb

drop table tb

/*
id          name       code         
----------- ---------- ------------ 
1           A          BH00001
2           B          BH00002
3           C          BH00003
4           D          BH00004

（所影响的行数为 4 行）
*/

[ACMAIN_CHM]

仍以 int 型的自动编号就行了，显示的时候控制一下把A加上,比如１１　显示为 A011

[q85893349]

三楼的代码有错误么？

[q85893349]

2楼的代码，如果删除了一个记录，前面的BH不动就好勒。

[wangxianshou]

IF OBJECT_ID('ABC') IS NOT NULL
DROP TABLE ABC
create table ABC
(
A VARCHAR(13) NOT NULL
,B VARCHAR(13) NULL
)
INSERT INTO ABC
SELECT 'A001','AFAFASF' UNION ALL
SELECT 'A002','AFAFASF'

SELECT * FROM ABC

INSERT INTO ABC
SELECT
'A'+
CASE
WHEN (SELECT COUNT(*) FROM ABC)<10 THEN '00'+Convert(varchar(13),(SELECT COUNT(*) FROM ABC)+1)
WHEN (SELECT COUNT(*) FROM ABC) BETWEEN 10 AND 100 THEN '0'+Convert(varchar(13),(SELECT COUNT(*) FROM ABC)+1)
ELSE Convert(varchar(13),(SELECT COUNT(*) FROM ABC)+1)
END as a
,'afasfafa'


INSERT INTO ABC
SELECT
'A'+
CASE
WHEN (SELECT COUNT(*) FROM ABC)<10 THEN '00'+Convert(varchar(13),(SELECT CAST (MAX(REPLACE(A,'A','')) AS INT) FROM ABC)+1)
WHEN (SELECT COUNT(*) FROM ABC) BETWEEN 10 AND 100 THEN '0'+Convert(varchar(13),(SELECT CAST(MAX(REPLACE(A,'A','')) AS INT) FROM ABC)+1)
ELSE Convert(varchar(13),(SELECT CAST (MAX(REPLACE(A,'A','')) AS INT) FROM ABC)+1)
END as a
,'afasfafa'
随手写的，楼主参考

[q85893349]

三楼的代码，比较符合我的要求，谢谢，结贴，散分。

[wangxianshou]

IF OBJECT_ID('ABC') IS NOT NULL
DROP TABLE ABC
create table ABC
(
A VARCHAR(13) NOT NULL
,B VARCHAR(13) NULL
)
INSERT INTO ABC
SELECT 'A001','AFAFASF' UNION ALL
SELECT 'A002','AFAFASF'

declare @Number as int

SET @Number=(SELECT CAST (MAX(REPLACE(A,'A','')) AS INT) FROM ABC)+1

declare @i as int
set @i=150
WHILE @i>0
begin
INSERT INTO ABC
SELECT
'A'+
CASE LEN(@Number)
WHEN 1 THEN '00'+CAST(@Number AS VARCHAR(13))
WHEN 2 THEN '0'+CAST(@Number AS VARCHAR(13))
WHEN 3 THEN CAST(@Number AS VARCHAR(13))
END AS NEXTNUMBER
,'KJAFLKAJKL'
SET @Number=(SELECT CAST (MAX(REPLACE(A,'A','')) AS INT) FROM ABC)+1
set @i=@i-1
end

SELECT * FROM ABC

还是以目前数据中最大的号往下编号吧，写的仓促，楼主参考