原来是这样的,这样排序,数据必须按照
'2011-1-1'
'2011-1-1'
'2011-1-2'
'2011-1-3'
'2011-1-3'
这种顺时顺序才行。
如果我的数据是乱的
'2011-1-1'
'2011-1-3'
'2011-1-2'
'2011-1-1'
'2011-1-3'排序出来就全部是1
'2011-1-1'
'2011-1-1'
'2011-1-2'
'2011-1-3'
'2011-1-3'
这种顺时顺序才行。
如果我的数据是乱的
'2011-1-1'
'2011-1-3'
'2011-1-2'
'2011-1-1'
'2011-1-3'排序出来就全部是1
先排序再更新
但是with里面不能排序。
--用临时表
select * into #tb from tb order by dateupdate #tb set ...
create table tb(id int identity(1,1),pdate datetime,info varchar(20) null)
insert into tb
select '2011-01-01 11:11:11',null union all
select '2011-01-01 21:11:11',null union all
select '2011-01-02 01:11:11',null union all
select '2011-01-02',null union all
select '2011-01-04',null union all
select '2011-01-02',null union all
select '2011-01-03',null union all
select '2011-01-04',null union all
select '2011-01-04',null union all
select '2011-01-04',null union all
select '2011-01-05',null
goselect *
into #tb
from tb
order by pdate,iddeclare @num int,@str varchar(10)
select @num=0, @str=''
update #tb
set info=convert(varchar(8),pdate,112)+right('0000'+rtrim(@num),5),
@num=(case when convert(varchar(8),pdate,112)=@str then @num+1 else 1 end),
@str=convert(varchar(8),pdate,112)update a
set a.info = b.info
from tb a,#tb b
where a.id = b.idselect *
from tb
order by pdate,iddrop table tb,#tb/***************id pdate info
----------- ----------------------- --------------------
1 2011-01-01 11:11:11.000 2011010100001
2 2011-01-01 21:11:11.000 2011010100002
4 2011-01-02 00:00:00.000 2011010200001
6 2011-01-02 00:00:00.000 2011010200002
3 2011-01-02 01:11:11.000 2011010200003
7 2011-01-03 00:00:00.000 2011010300001
5 2011-01-04 00:00:00.000 2011010400001
8 2011-01-04 00:00:00.000 2011010400002
9 2011-01-04 00:00:00.000 2011010400003
10 2011-01-04 00:00:00.000 2011010400004
11 2011-01-05 00:00:00.000 2011010500001(11 行受影响)