在学习中遇到这个问题 数据库里有编号字段 BH00001 BH00002 BH00003 BH00004 如何实现自动增长 --下面的代码生成长度为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 --*/ create table tb (id int identity, name varchar(10), 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 tbdrop table tb/* id name code ----------- ---------- ------------ 1 A BH00001 2 B BH00002 3 C BH00003 4 D BH00004(所影响的行数为 4 行) */
DECLARE @X INT SET @X=1SELECT 'HF'+RIGHT('000000'+CAST(@X AS VARCHAR),5)
Id, FormatId, F1 ,F2 Id序号我设了自动加一,FormatId我想他也象这样"SL000001", 当Insert时就加1,FormatId我想他也能自动加一"SL000001","SL000002"... 能用一条sql什么办法实现.最好不要用中间表。有什么好方法? 谢谢! create table #test (id int identity, FormatId as 'SL'+right(10000000+id,6), F1 varchar(50)) go insert #test(F1) select '1' union all select '2' select * from #testdrop table #test /* id FormatId F1 ----------- -------------- ----- 1 SL000001 1 2 SL000002 2(所影响的行数为 2 行) */
可以模仿oracle的做法: --创建流水号控制表,并插入数据 if object_id('sequence') is not null drop table sequence go
create table sequence(tablename sysname primary key,sn bigint not null) goset nocount on insert sequence values('test',1) go--创建获取当前 序列 值的存储过程,该过程为一基础的过程。其他获取具体格式流水号的过程在此基础上构建 if object_id('get_sn') is not null drop procedure get_sn go create procedure get_sn @tablename sysname, @sn bigint output as begin set nocount on select @sn=sn from sequence where tablename=@tablename update sequence set sn=@sn+1 where tablename=@tablename end go --创建获取流水号的存储过程 if object_id('test_liushuihao') is not null drop procedure test_liushuihao go create procedure test_liushuihao @test_sn varchar(20) output --格式:HF00001,HF00002...........HF00010 as begin set nocount on declare @sn bigint declare @tp varchar(10) exec get_sn 'test',@sn output set @tp=cast(@sn as varchar(10)) set @test_sn = 'HF' + replicate('0',5-len(@tp)) + @tp end go --测试 declare @test_sn varchar(10) exec test_liushuihao @test_sn output select @test_sn as current_value
数据库里有编号字段
BH00001
BH00002
BH00003
BH00004
如何实现自动增长 --下面的代码生成长度为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
--*/ create table tb
(id int identity,
name varchar(10),
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 tbdrop table tb/*
id name code
----------- ---------- ------------
1 A BH00001
2 B BH00002
3 C BH00003
4 D BH00004(所影响的行数为 4 行)
*/
DECLARE @X INT
SET @X=1SELECT 'HF'+RIGHT('000000'+CAST(@X AS VARCHAR),5)
Id序号我设了自动加一,FormatId我想他也象这样"SL000001",
当Insert时就加1,FormatId我想他也能自动加一"SL000001","SL000002"...
能用一条sql什么办法实现.最好不要用中间表。有什么好方法?
谢谢!
create table #test
(id int identity,
FormatId as 'SL'+right(10000000+id,6),
F1 varchar(50))
go
insert #test(F1) select '1'
union all select '2'
select * from #testdrop table #test
/*
id FormatId F1
----------- -------------- -----
1 SL000001 1
2 SL000002 2(所影响的行数为 2 行)
*/
--创建流水号控制表,并插入数据
if object_id('sequence') is not null
drop table sequence
go
create table sequence(tablename sysname primary key,sn bigint not null)
goset nocount on
insert sequence values('test',1)
go--创建获取当前 序列 值的存储过程,该过程为一基础的过程。其他获取具体格式流水号的过程在此基础上构建
if object_id('get_sn') is not null
drop procedure get_sn
go
create procedure get_sn
@tablename sysname,
@sn bigint output
as
begin
set nocount on
select @sn=sn from sequence where tablename=@tablename
update sequence set sn=@sn+1 where tablename=@tablename
end
go
--创建获取流水号的存储过程
if object_id('test_liushuihao') is not null
drop procedure test_liushuihao
go
create procedure test_liushuihao
@test_sn varchar(20) output --格式:HF00001,HF00002...........HF00010
as
begin
set nocount on
declare @sn bigint
declare @tp varchar(10)
exec get_sn 'test',@sn output
set @tp=cast(@sn as varchar(10))
set @test_sn = 'HF' + replicate('0',5-len(@tp)) + @tp
end
go
--测试
declare @test_sn varchar(10)
exec test_liushuihao @test_sn output
select @test_sn as current_value