数据如下:
aaa bbb
1 a
1 c
1 g
2 k
2 b
2 a查询成
aaa bbb
1 a 1
1 c 2
1 g 3
2 a 1
2 b 2
2 k 3oracle语句如下
select row_number() over(partition by aaa order by bbb) from abc
aaa bbb
1 a
1 c
1 g
2 k
2 b
2 a查询成
aaa bbb
1 a 1
1 c 2
1 g 3
2 a 1
2 b 2
2 k 3oracle语句如下
select row_number() over(partition by aaa order by bbb) from abc
insert into @t select 1,'a'
union all select 1,'c'
union all select 1,'g'
union all select 2,'k'
union all select 2,'b'
union all select 2,'a'select id_t=identity(int,1,1),* into # from @t
select aaa,bbb,num=(select count(*)+1 from # where aaa=a.aaa and id_t<a.id_t) from # adrop table #--这样?
insert into @t select 1,'a'
union all select 1,'c'
union all select 1,'g'
union all select 2,'k'
union all select 2,'b'
union all select 2,'a'select id_t=identity(int,1,1),* into # from @t order by aaa,bbb
select aaa,bbb,num=(select count(*)+1 from # where aaa=a.aaa and id_t<a.id_t) from # adrop table #
table1 where aaa=a.aaa and bbb<a.bbb)+1
from table1 as a
create table t1 (aaa int,bbb char)
insert into t1
select 1,'a'
union all
select 1,'c'
union all
select 1,'g'
union all
select 2,'k'
union all
select 2,'b'
union all
select 2,'a'
select A.aaa,A.bbb,ss=(select count(1)+1 from t1 where aaa=A.aaa and bbb<A.bbb ) from t1 A
drop table t1