SQL流水号生成语句

table
     
        Num        createDate
---------------------------------
      (空)          20090901
      (空)          20090901
      (空)          20090902
      (空)          20090902
      (空)          20090903
      (空)          20090903
---------------------------------
      现在要通过createDate来产生一个流水号(4位) 按当天来生成 换天要重新生成
      即得到结果如下:
table
     
        Num        createDate
---------------------------------
    200909010001    20090901
    200909010002    20090901           
    200909020001    20090902
    200909020002    20090902
    200909030001    20090903
    200909030002    20090903
---------------------------------

 

 

 

create table tb(id int , Num varchar(12),createDate varchar(8))
insert into tb values(1,'','20090901')
insert into tb values(2,'','20090901')
insert into tb values(3,'','20090902')
insert into tb values(4,'','20090902')
insert into tb values(5,'','20090903')
insert into tb values(6,'','20090903')
go

update tb set num = createDate + right('000'+cast((select count(1) from tb where createDate = t.createDate and id < t.id) + 1 as varchar),4) from tb t

select * from tb

drop table tb

/*
id          Num          createDate
----------- ------------ ----------
1           200909010001 20090901
2           200909010002 20090901
3           200909020001 20090902
4           200909020002 20090902
5           200909030001 20090903
6           200909030002 20090903

（所影响的行数为 6 行）

*/

 

declare @tb1 table([createDate] datetime)
insert @tb1
select '20090901' union all
select '20090901' union all
select '20090902' union all
select '20090902' union all
select '20090903' union all
select '20090903'

--SQL 2005
select convert(nvarchar(20),[createDate],112) as  [createDate],convert(nvarchar(20),[createDate],112)+right('0000'+cast(row_number() over (partition by [createDate] order by [createDate]) as nvarchar(10)),4) as num
from @tb1

--SQL2000
select identity(int,1,1) as id,[createDate]
into #tem            
from @tb1

select convert(nvarchar(20),[createDate],112) as  [createDate],convert(nvarchar(20),[createDate],112)+right('0000'+cast((select count(1) from #tem where t.[createDate]=[createDate] and t.id > id)+1 as nvarchar(10)),4) as num
from #tem t

drop table #tem


--测试结果:
/*
createDate           num
-------------------- ------------------------
20090901             200909010001
20090901             200909010002
20090902             200909020001
20090902             200909020002
20090903             200909030001
20090903             200909030002

(6 row(s) affected)

*/