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 2009