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.c3
and a.c1=b.c1 and a.c1 =1
) order_num
from t1 b
where  c1 =1
order 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.c3
and a.c1=b.c1
) order_num
from t1 b
order 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  

以上就是【查询数据排名情况SQL】的全部内容了,欢迎留言评论进行交流!