手机
当前位置:查字典教程网 >编程开发 >mssql数据库 >查询数据排名情况SQL
查询数据排名情况SQL
摘要: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

【查询数据排名情况SQL】相关文章:

sqlserver数据库迁移的几种方式

sql2005 数据同步方法

SQL语句查询重复字段并按数量排序

查找sqlserver数据库中某一字段在 哪

T-SQL入門攻略之查看数据库对象

MSSQL2005数据库备份导入MSSQL2000

MSSQL 清空数据库的方法

恢复.mdf 数据库步骤

数据库分页存储过程代码

查询sql数据库中表占用的空间大小

精品推荐
分类导航