--排列名次的sql server函数-------------（原创）

--排列位置的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