有点难 求一个流水号生成语句 (50分)table表里有 两个字段, Num---流水号 createDate----创建日期(int 例:20090911)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
---------------------------------跪求这种语句该如何写。 请各位大侠帮忙。 感激不尽~~50尽献!!
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
---------------------------------跪求这种语句该如何写。 请各位大侠帮忙。 感激不尽~~50尽献!!
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'select * ,convert(nvarchar(20),[createDate],112)+right('0000'+cast(row_number() over (partition by [createDate] order by [createDate]) as nvarchar(10)),4)
from @tb1
--测试结果:
/*
----------------------- ------------------------
2009-09-01 00:00:00.000 200909010001
2009-09-01 00:00:00.000 200909010002
2009-09-02 00:00:00.000 200909020001
2009-09-02 00:00:00.000 200909020002
2009-09-03 00:00:00.000 200909030001
2009-09-03 00:00:00.000 200909030002(6 row(s) affected)*/
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')
goupdate 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 tselect * from tbdrop table tb/*
id Num createDate
----------- ------------ ----------
1 200909010001 20090901
2 200909010002 20090901
3 200909020001 20090902
4 200909020002 20090902
5 200909030001 20090903
6 200909030002 20090903(所影响的行数为 6 行)*/
into #temp
from table
结贴~!
insert into tb values('','20090901')
insert into tb values('','20090901')
insert into tb values('','20090902')
insert into tb values('','20090902')
insert into tb values('','20090903')
insert into tb values('','20090903')
goselect * , id = identity(int,1,1) into tmp from tbupdate tmp set num = createDate + right('000'+cast((select count(1) from tmp where createDate = t.createDate and id < t.id) + 1 as varchar),4) from tmp tdelete from tbinsert into tb select num , createdate from tmpselect * from tbdrop table tb , tmp/*
Num createDate
------------ ----------
200909010001 20090901
200909010002 20090901
200909020001 20090902
200909020002 20090902
200909030001 20090903
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 @tb1select 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 tdrop table #tem
--测试结果:
/*
createDate num
-------------------- ------------------------
20090901 200909010001
20090901 200909010002
20090902 200909020001
20090902 200909020002
20090903 200909030001
20090903 200909030002(6 row(s) affected)*/