当前位置： 移动技术网 > IT编程>数据库>MSSQL > 查询数据排名情况SQL

查询数据排名情况SQL

2017年12月12日  | 移动技术网IT编程  | 我要评论

万象2004 940破解版,dgl-031,魔兽斧王之肆无忌惮

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  

 

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网