--如何生成流水号:
go
if OBJECT_ID('test')is not null
drop table test
go
create table test(
BH VARCHAR(15),
A varchar(10),
B varchar(10),
C varchar(10)
)
go
insert test
select '','a','b','c' union all
select '','d','e','f' union all
select '','g','h','i' union all
select '','j','k','l' union all
select '','m','n','o' union all
select '','p','q','r'

--创建函数：
go
if OBJECT_ID('fun_tracy')is not null
drop function fun_tracy
go
create function fun_tracy (@id int)
returns varchar(15)
as
begin
declare @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 @BH
end


--处理表数据:
alter table test add id int identity
go

update test
set BH=dbo.fun_tracy(id)
ALTER TABLE TEST DROP COLUMN ID
GO
select * from test
/*
BH    A    B    C
OP12042700001    a    b    c
OP12042700002    d    e    f
OP12042700003    g    h    i
OP12042700004    j    k    l
OP12042700005    m    n    o
OP12042700006    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 tba
END
GO
CREATE TABLE Tba
(
    TDate VARCHAR(10),
    Num VARCHAR(100)
) 

GO


INSERT INTO Tba
SELECT '2012-04-28','OP12042800001' UNION
SELECT '2012-04-28','OP12042800002' UNION
SELECT '2012-04-28','OP12042800003' UNION
SELECT '2012-04-28','OP12042800004' 

GO


IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'InsertNum')
BEGIN
    DROP FUNCTION InsertNum
END
GO
CREATE FUNCTION InsertNum(@TDate VARCHAR(10))
RETURNS VARCHAR(100)
AS

BEGIN
DECLARE @Num VARCHAR(100)
DECLARE @Num_Int INT
SET @TDate = REPLACE(@TDate,'-','')
SET @TDate = RIGHT(@TDate,6)

SELECT @Num_Int = MAX(CAST(RIGHT(Num,5) AS INT)) + 1
FROM Tba
WHERE SUBSTRING(Num,3,6) = @TDate

IF @Num_Int IS NULL 
BEGIN
    SET @Num_Int = 1
END

SET @Num = 'OP' + @TDate + REPLICATE('0',5-LEN(CAST(@NUm_int AS VARCHAR(10)))) + CAST(@NUm_int AS VARCHAR(10))


RETURN @Num
END

GO

INSERT INTO Tba 
SELECT '2012-04-28',DBO.InsertNum('2012-04-28') UNION
SELECT '2012-04-29',DBO.InsertNum('2012-04-29')

SELECT * FROM tba

TDate    Num
2012-04-28    OP12042800001
2012-04-28    OP12042800002
2012-04-28    OP12042800003
2012-04-28    OP12042800004
2012-04-28    OP12042800005
2012-04-29    OP12042900001

------解决方案--------------------

SQL code

创建生成流水号的触发器

create table test(id varchar(18),  --流水号,日期(8位)+时间(4位)+流水号(4位)
name varchar(10)  --其他字段
)

go
--创建生成流水号