1/准备测试数据
---------------------------------------------------------------------------------create table t1(c1 integer,c2 integer,c3 integer);
insert into t1 values(1,2,3)
insert into t1 values(1,8,4)insert into t1 values(1,4,4)
insert into t1 values(1,4,5)
insert into t1 values(1,5,5)
insert into t1 values(2,2,3)
insert into t1 values(2,8,4)insert into t1 values(2,4,4)
insert into t1 values(2,4,5)
insert into t1 values(2,5,5)
2/查看排名
---------------------------------------------------------------------------------
A/单记录排名
select c1,c3,(select count( c3)+1 from t1 a where a.c3>b.c3and a.c1=b.c1 and a.c1 =1) order_numfrom t1 bwhere c1 =1order by c1,c3
c1 c3 order_num ----------- ----------- ---------------------- 1 3 5 1 4 3 1 4 3 1 5 1 1 5 1 B/多记录排名
select c1,c2,c3,(select count( c3)+1 from t1 a where a.c3>b.c3and a.c1=b.c1) order_numfrom t1 border by c1,c3
c1 c2 c3 order_num ----------- ----------- ----------- ---------------------- 1 2 3 5 1 8 4 3 1 4 4 3 1 4 5 1 1 5 5 1 2 2 3 5 2 8 4 3 2 4 4 3 2 4 5 1 2 5 5 1