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