SQL Server 2000 中的排名查询

引言

SQL Server 2000 中获取表中特定数字字段排名的 SQL 查询。目前在 SQL Server 2000 中有两种方法可以获取排名。

(1) Rank 函数：Rank((c,d), {(a,b), (e,f), (c,d)}) 但它不足以满足所需的结果集。

(2)SQL Server 2000 全文检索服务，这用于文本，不能给出正确的排名结果。

以下查询基于解决方案，用于获取表中数字字段的排名结果。

使用代码

SQL Server 2000 没有为表的数字字段提供任何内置的排名功能。这现在在 SQL Server 2005 中可用。

如果我们需要对特定数字字段进行排名，可以通过两种方式满足我们的需求

(1) 使用编程语言。通过 For 循环、While 循环。或者在表中添加另一个专门用于排名的字段。

(2) 使用 SQL 查询。

使用 SQL 查询获取排名比其他解决方案更有效。

例如，有一个在线竞赛的应用程序。每个用户获得积分。现在我们想要获取前十名用户的列表。在列表中，可能存在多个用户具有相同的积分。因此，我们必须为所有具有相同积分的用户分配相同的排名。在最终的前 10 名用户结果集中，总结果可能是 10 个或多于 10 个。

假设表包含 3 个字段

字段名	类型
pk_ID	int
名称	varchar
点	int

以及此表中的数据

pk_ID	名称	Point
1	abc	250
2	xyz	150
3	pqr	350
4	stu	200
5	vwx	270
6	srk	250

现在根据要求，我们想要获取用户姓名列表，以及他们的积分和排名。输出结果如下表所示。这里用户姓名 'pqr' 具有最高的积分，因此排名第一，用户姓名 'abc' 和 'srk' 具有相同的积分，排名相同。

pk_ID	名称	Point	排名
3	pqr	350	1
5	vwx	270	2
6	srk	250	3
1	abc	250	3
4	stu	200	4
2	xyz	150	5

以下是获取此类型结果的查询

-- Create one table

CREATE TABLE [dbo].[rank] (
    [pk_id] [int] IDENTITY (1, 1) NOT NULL ,
    [name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [point] [int] NOT NULL 
) ON [PRIMARY]
GO

-- Insert some value in it.

insert into rank (name, point) values ('abc',250)
insert into rank (name, point) values ('xyz',150)
insert into rank (name, point) values ('pqr',350)
insert into rank (name, point) values ('stu',200)
insert into rank (name, point) values ('vwx',270)
insert into rank (name, point) values ('srk',250)

-- Apply this query to get rank

select [R1].[pk_id],[R1].[name], [R1].[point], rank = (select count(distinct [R2].[point]) from [rank] [R2] where [R1].[point] <= [R2].[Point]) from [rank] [R1]
order by [rank]

-- Apply this query to get Top 5 rank

select Top 5 [R1].[pk_id],[R1].[name], [R1].[point], rank = (select count(distinct [R2].[point]) from [rank] [R2] where [R1].[point] <= [R2].[Point]) from [rank] [R1]
order by [rank]

-- Get rank of  single user e.g. 'srk'

select [R1].[pk_id],[R1].[name], [R1].[point], rank = (select count(distinct [R2].[point]) from [rank] [R2] where [R1].[point] <= [R2].[Point]) from [rank] [R1]
where name = 'srk' order by [rank]