HOA00001
HOA00002
HOA00003
HOA00004
HOA00005
HOA00006
HOA00007
HOA00008
HOA00009
HOA00010
HOA00011
想知道这样的自增的sql语句该怎么去写,请哪位大虾帮个忙
HOA00002
HOA00003
HOA00004
HOA00005
HOA00006
HOA00007
HOA00008
HOA00009
HOA00010
HOA00011
想知道这样的自增的sql语句该怎么去写,请哪位大虾帮个忙
create table A_T(iId int identity(1,1) not null, Code varchar(20))
Go/*
功能描述:根获得流水号
参数:@Fix 流水号的前缀
@Len 流水号的长度,不指定,则已最后一流水号的长度为基准,若是不存在已有的流水号,在不指定长度
返回值:返回流水号
*/
create function uf_GrentCode(@Fix varchar(10), @Len int)
Returns Varchar(20)
AS
Begin
declare @RetVal varchar(20), @LastCode varchar(20)
declare @LenFix int, @LastNum int, @Param varchar(20)
set @LenFix = len(@Fix)
if not exists(select 1 from A_T)
set @LastNum = 1
else
begin
select top 1 @LastCode=Code, @Len=Len(@LastCode) - @LenFix from A_T order by iId desc
set @LastNum = convert(int, substring(@LastCode, @LenFix+1, len(@LastCode)-@LenFix))+1
end if not exists (select 1 from A_T where Code like @Fix+'%')
set @LastNum = 1 set @Param = Power(10, @Len - Len(@LastNum))
set @Param = substring(@Param, 2, len(@Param)-1) set @RetVal = @Fix+@Param+cast(@LastNum as varchar) Return @RetVal
End;
Goinsert A_T (Code) select dbo.uf_GrentCode('HOA', 5)select * from A_T
declare @str varchar(8) = 'HOA',
@id smallint
select @id = convert(smallint,right(max(col),5)) from tab
if @id is nulll
set @id = 1
else
set @id = @id + 1select @str = @str + right('00000'+convert(varchar(5),@id),5)
DECLARE @id INT
SET @id=1
WHILE @id<=10
BEGIN SELECT 'HOA'+RIGHT('00000'+CONVERT(VARCHAR(5),@id),5)
SET @id=@id+1END
[code=SQL]
create table tb(id int identity(1,1),code as right('HOA'+ltrim(100000+id),8),col varchar(7),...)[/code]
将自增的工作交给数据库来完成
写错。
create table tb(id int identity(1,1),code as 'HOA'+right(ltrim(100000+id),5),col varchar(7),...)
--得到新编号的函数
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
--*/