查询数据排名情况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,5)

insert into t1 values(1,5,5)

insert into t1 values(2,3)

insert into t1 values(2,4)
insert into t1 values(2,4)

insert into t1 values(2,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,
(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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!