select 1 as Col1,'3344' as Col2 into temp
insert into temp select 2 ,'3344'
insert into temp select 3 ,'3344'
insert into temp select 4 ,'5566'
insert into temp select 5 ,'5566'
insert into temp select 6 ,'7788'
insert into temp select 7 ,'7788' select Col1,Col2,(select count(distinct Col2) from temp as T2 where T2.Col2<=T1.Col2 and T2.Col1<=T1.Col1) from temp as T1 order by Col2
drop table temp查询结果如下:
1 3344 1
2 3344 1
3 3344 1
5 5566 2
4 5566 2
6 7788 3
7 7788 3
insert into temp select 2 ,'3344'
insert into temp select 3 ,'3344'
insert into temp select 4 ,'5566'
insert into temp select 5 ,'5566'
insert into temp select 6 ,'7788'
insert into temp select 7 ,'7788' select Col1,Col2,(select count(distinct Col2) from temp as T2 where T2.Col2<=T1.Col2 and T2.Col1<=T1.Col1) from temp as T1 order by Col2
drop table temp查询结果如下:
1 3344 1
2 3344 1
3 3344 1
5 5566 2
4 5566 2
6 7788 3
7 7788 3
from tablename
--源表如下
1 a344
2 a344
3 a344
5 55dd
4 55dd
6 ff88
7 ff88 同样要得到以下结果,又如何呢?1 a344 1
2 a344 1
3 a344 1
5 55dd 2
4 55dd 2
6 ff88 3
7 ff88 3
.......