查询数据排名情况SQL

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的全部内容，更多相关查询数据排名情况SQL内容请搜索靠谱客的其他文章。