求助-关于生成流水号
现在有两张表,一个是主表TB,一个是从外部导入进来的临时表TB1
把临时表的数据插入到主表中,主表中有一个流水号字段,
流水号字段生成规则
类型+年+月+日+5位流水号(如OP12042700001)
有一个条件,每天生成的后五位流水号,都是从0001开始.
先谢过!
[解决办法]
--如何生成流水号:goif OBJECT_ID('test')is not nulldrop table testgocreate table test(BH VARCHAR(15),A varchar(10),B varchar(10),C varchar(10))goinsert testselect '','a','b','c' union allselect '','d','e','f' union allselect '','g','h','i' union allselect '','j','k','l' union allselect '','m','n','o' union allselect '','p','q','r'--创建函数：goif OBJECT_ID('fun_tracy')is not nulldrop function fun_tracygocreate function fun_tracy (@id int)returns varchar(15)asbegindeclare @BH varchar(15)set @BH=''declare @date varchar(10)select @date='OP'+right(replace(convert(varchar(10),getdate(),120),'-',''),6)select @BH=@date+right('00000'+ltrim(@id),5)return @BHend--处理表数据:alter table test add id int identitygoupdate testset BH=dbo.fun_tracy(id)ALTER TABLE TEST DROP COLUMN IDGOselect * from test/*BH A B COP12042700001 a b cOP12042700002 d e fOP12042700003 g h iOP12042700004 j k lOP12042700005 m n oOP12042700006 p q r*/--这个可否满足？？
[解决办法]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin tran
select @vNextCommLsh = '0'
select @CurrDate = convert(varchar(8), getdate(), 112), @CurrCommLsh = CurrCommLsh from Syssetup where maxuser=1000-- with (tablockx)
select @MaxCommLsh = isnull(max(lsh), '0') from CardUse where substring(lsh, 1 ,8) = @CurrDate
while @vNextCommLsh <= @MaxCommLsh
begin
if @CurrDate = substring(@CurrCommLsh, 1, 8)
select @vNextCommLsh = @CurrDate + substring(convert(varchar(10),100000001+convert(int, substring(@CurrCommLsh, 9, 8))), 2, 8)
--select @vNextCommLsh = @CurrDate + substring(100000001+convert(bigint, '111'), 2, 8)
else
select @vNextCommLsh = @CurrDate + '00000001'
select @CurrCommLsh = @vNextCommLsh
end
update syssetup with(rowlock) set CurrCommLsh = @vNextCommLsh where maxuser=1000
if @@Error <> 0
rollback tran
else
commit tran
[解决办法]
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROp TABLE tbaENDGOCREATE TABLE Tba( TDate VARCHAR(10), Num VARCHAR(100)) GOINSERT INTO TbaSELECT '2012-04-28','OP12042800001' UNIONSELECT '2012-04-28','OP12042800002' UNIONSELECT '2012-04-28','OP12042800003' UNIONSELECT '2012-04-28','OP12042800004' GOIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'InsertNum')BEGIN DROP FUNCTION InsertNumENDGOCREATE FUNCTION InsertNum(@TDate VARCHAR(10))RETURNS VARCHAR(100)ASBEGINDECLARE @Num VARCHAR(100)DECLARE @Num_Int INTSET @TDate = REPLACE(@TDate,'-','')SET @TDate = RIGHT(@TDate,6)SELECT @Num_Int = MAX(CAST(RIGHT(Num,5) AS INT)) + 1FROM TbaWHERE SUBSTRING(Num,3,6) = @TDateIF @Num_Int IS NULL BEGIN SET @Num_Int = 1ENDSET @Num = 'OP' + @TDate + REPLICATE('0',5-LEN(CAST(@NUm_int AS VARCHAR(10)))) + CAST(@NUm_int AS VARCHAR(10))RETURN @NumENDGOINSERT INTO Tba SELECT '2012-04-28',DBO.InsertNum('2012-04-28') UNIONSELECT '2012-04-29',DBO.InsertNum('2012-04-29')SELECT * FROM tbaTDate Num2012-04-28 OP120428000012012-04-28 OP120428000022012-04-28 OP120428000032012-04-28 OP120428000042012-04-28 OP120428000052012-04-29 OP12042900001
[解决办法]
创建生成流水号的触发器create table test(id varchar(18), --流水号,日期(8位)+时间(4位)+流水号(4位)name varchar(10) --其他字段)go--创建生成流水号的触发器create trigger t_inserton testINSTEAD OF insertasdeclare @id varchar(18),@id1 int,@head varchar(12)select * into #tb from insertedset @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','')select @id=max(id) from test where id like @head+'%'if @id is nullset @id1=0elseset @id1=cast(substring(@id,13,4) as int)update #tb set @id1=@id1+1,id=@head+right('0000'+cast(@id1 as varchar),4)insert into test select * from #tbgo--插入数据,进行测试insert into test(name)select 'aa'union all select 'bb'union all select 'cc'--修改系统时间,再插入数据测试一次insert into test(name)select 'aa'union all select 'bb'union all select 'cc'--显示测试结果select * from test--删除测试环境drop table test
[解决办法]
create table test(id varchar(18), --流水号,日期(8位)+时间(4位)+流水号(4位)name varchar(10) --其他字段)go--创建生成流水号的触发器create trigger t_inserton testINSTEAD OF insertasdeclare @id varchar(18),@id1 int,@head varchar(12)select * into #tb from insertedset @head='OP'+right(replace(convert(varchar(10),getdate(),120),'-',''),6)select @id=max(id) from test where id like @head+'%'if @id is nullset @id1=0elseset @id1=cast(substring(@id,13,4) as int)update #tb set @id1=@id1+1,id=@head+right('0000'+cast(@id1 as varchar),4)insert into test select * from #tbgo--插入数据,进行测试insert into test(name) --系统日期为了2012-04-28select 'aa'union all select 'bb'union all select 'cc'goselect * from test/*id name------------------ ----------OP1204280001 aaOP1204280002 bbOP1204280003 cc(3 行受影响)*/--修改系统时间,再插入数据测试一次insert into test(name) --系统日期为了2012-04-29select 'aa'union all select 'bb'union all select 'cc'goselect * from test/*id name------------------ ----------OP1204280001 aaOP1204280002 bbOP1204280003 ccOP1204280001 aaOP1204280002 bbOP1204280003 cc(6 行受影响)*/--删除测试环境drop table test
[解决办法]
--测试数据2,没有修改系统时间,所以显示结果没有变select * from test/*id name------------------ ----------OP1204290001 aaOP1204290002 bbOP1204290003 ccOP1204280001 aaOP1204280002 bbOP1204280003 cc(6 行受影响)*/