查询数据排名情况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语句查询出某个学生的姓名、成绩以及在表中的排名

MySQL中给自定义的字段查询结果添加排名的方法

SQL查询排名函数实例