对数据库中的纪录进行排名

例如，给一个班的学生的成绩进行排名，相同分数的排名相同

方法一：

create table Test
(F1 char(10),
 F2 char(10))

--测试表

insert into Test
select 'a' F1,'1' F2
union
select 'b' F1,'2' F2
union
select 'c' F1,'3' F2
union
select 'd' F1,'3' F2
union
select 'e' F1,'4' F2
union
select 'f' F1,'4' F2
union
select 'g' F1,'4' F2
union
select 'h' F1,'7' F2
union
select 'i' F1,'9' F2

--插入数据

select id=identity(int,0,1),f1,f2 into #t from test order by F2 desc

select a.f1,a.f2,a.id+1-cast(id-cc-minn as Char(10)) as  [名次]
from #t a,
(select f2,cc,minn  from
  (select  f2,count(*)as cc,min(id)-count(*) as minn from #t group by f2) t)b
where a.f2=b.f2
order by a.f2 desc

--测试

drop table #t
drop table test

方法二：

--排列位置的sql server函数-------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create   procedure up_get_sort
      @ls_o  varchar(10)
as
declare @ll_sort int,
 @ldc_value dec(9,2),
 @ls_id varchar(16),
 @ll_same int,
 @ls_sql varchar(200)
 
 

Begin

---排位计算-------
-----------------------
---有这么一个规则 数值相同的排名相同，
---而且占有位置例如第1名分数相同有3人,那么第2名就从4名开始------
--======================---------------
--开始之前要把关键字和数值保存到exam_use_sort表里
set @ll_sort =1

if lower(@ls_o) = 'desc' or lower(@ls_o)='d'
begin--降序
set @ls_sql = ' SELECT id,use_value FROM exam_use_sort  order by isnull(use_value,0) desc'

end
else
begin--升序
set @ls_sql = ' SELECT id,use_value FROM exam_use_sort  order by isnull(use_value,0)'
end

exec('declare  cur  cursor for '+@ls_sql)

OPEN cur ;

fetch next from cur into @ls_id,@ldc_value;

WHILE @@FETCH_STATUS = 0
BEGIN
update exam_use_sort set use_sort = @ll_sort where
 use_value = @ldc_value;
--有多少条相同的
select @ll_same = isnull(count(*),0) from exam_use_sort
where    use_value = @ldc_value;

set @ll_sort = @ll_sort + @ll_same

fetch next from cur into @ls_id,@ldc_value;

END
CLOSE cur;
DEALLOCATE cur;

End

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-------函数用到得表------------------
CREATE TABLE [exam_use_sort] (
 [id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [use_value] [decimal](18, 2) NULL ,
 [use_sort] [int] NULL
) ON [PRIMARY]
GO


(根据CSDN摘录)