-关于生成流水号

--如何生成流水号: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 @[email protected]+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
------解决方案--------------------
SQL code
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
------解决方案--------------------
SQL code
创建生成流水号的触发器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 @[email protected]+1,[email protected]+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