查询数据排名情况SQL
查询数据排名情况SQL
发布时间:2016-12-29 来源:查字典编辑
摘要:1/准备测试数据--------------------------------------------------------------...

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

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新mssql数据库学习
热门mssql数据库学习
编程开发子分类