如何给组内的每项加一个编号
已知,
itemdatetemp
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
如何得到如下结果：
itemdatetemp
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, datedatetime, 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
(10 row(s) affected)
[解决办法]
select *,row_number() over(partition by item order by date ) as t from 表
[解决办法]
update T
set temp=(select count(*) from T where item=T.item and [date]> T.[date])+1
[解决办法]
考，2005完全抄袭oracle的函数，写法和oracle的一模一样，faint
