如何给组内的每项加一个编号
已知,

item date temp
10 '2006-01-01 ' 0
10 '2006-02-01 ' 0
10 '2006-03-01 ' 0
20 '2006-01-01 ' 0
20 '2006-02-01 ' 0
30 '2006-01-01 ' 0
30 '2006-02-01 ' 0
30 '2006-03-01 ' 0
30 '2006-04-01 ' 0
30 '2006-05-01 ' 0

如何得到如下结果：

item date temp
10 '2006-01-01 ' 1
10 '2006-02-01 ' 2
10 '2006-03-01 ' 3
20 '2006-01-01 ' 1
20 '2006-02-01 ' 2
30 '2006-01-01 ' 1
30 '2006-02-01 ' 2
30 '2006-03-01 ' 3
30 '2006-04-01 ' 4
30 '2006-05-01 ' 5

------解决方案--------------------
update T
set temp=(select count(*) from T a where a.item=T.item and a.[date] <=T.[date])
------解决方案--------------------
支持楼上
------解决方案--------------------
update T
set temp=(select count(*) from T a where a.item=T.item and a.[date]> =T.[date])

------解决方案--------------------
create table tab(item int, date datetime, temp int)
insert tab select 10, '2006-01-01 ', 0
union all select 10, '2006-02-01 ', 0
union all select 10, '2006-03-01 ', 0
union all select 20, '2006-01-01 ', 0
union all select 20, '2006-02-01 ', 0
union all select 30, '2006-01-01 ', 0
union all select 30, '2006-02-01 ', 0
union all select 30, '2006-03-01 ', 0
union all select 30, '2006-04-01 ', 0
union all select 30, '2006-05-01 ', 0

select id=identity(int,1,1), * into # from tab
select item,date,(select count(1) from # b where b.id <=a.id and b.item=a.item and b.date=a.date) from # a
drop table #
------解决方案--------------------

create table T(item int, [date] datetime, [temp] int)
insert T select 10, '2006-01-01 ', 0
union all select 10, '2006-02-01 ', 0
union all select 10, '2006-03-01 ', 0
union all select 20, '2006-01-01 ', 0
union all select 20, '2006-02-01 ', 0
union all select 30, '2006-01-01 ', 0
union all select 30, '2006-02-01 ', 0
union all select 30, '2006-03-01 ', 0
union all select 30, '2006-04-01 ', 0
union all select 30, '2006-05-01 ', 0

select item, [date], [temp]=(select count(*) from T where item=tmp.item and [date] <=tmp.[date])
from T as tmp

--result
item date temp
----------- ------------------------------------------------------ -----------
10 2006-01-01 00:00:00.000 1
10 2006-02-01 00:00:00.000 2
10 2006-03-01 00:00:00.000 3
20 2006-01-01 00:00:00.000 1
20 2006-02-01 00:00:00.000 2
30 2006-01-01 00:00:00.000 1
30 2006-02-01 00:00:00.000 2
30 2006-03-01 00:00:00.000 3
30 2006-04-01 00:00:00.000 4
30 2006-05-01 00:00:00.000 5