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

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

--排列位置的sqlserver函数-------------
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO


create  procedureup_get_sort
     @ls_o varchar(10)
as
declare@ll_sortint,
 @ldc_valuedec(9,2),
 @ls_idvarchar(16),
 @ll_sameint,
 @ls_sqlvarchar(200)
 
 

Begin

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

iflower(@ls_o)='desc'orlower(@ls_o)='d'
begin--降序
set@ls_sql='SELECTid,use_valueFROMexam_use_sort orderbyisnull(use_value,0)desc'


end
else
begin--升序
set@ls_sql='SELECTid,use_valueFROMexam_use_sort orderbyisnull(use_value,0)'
end

exec('declare cur cursorfor'+@ls_sql)

OPENcur;

fetchnextfromcurinto@ls_id,@ldc_value;

WHILE@@FETCH_STATUS=0
BEGIN
updateexam_use_sortsetuse_sort=@ll_sortwhere
 use_value=@ldc_value;
--有多少条相同的
select@ll_same=isnull(count(*),0)fromexam_use_sort
where   use_value=@ldc_value;

set@ll_sort=@ll_sort+@ll_same

fetchnextfromcurinto@ls_id,@ldc_value;

END
CLOSEcur;
DEALLOCATEcur;

End

 


GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO

-------函数用到得表------------------
CREATETABLE[exam_use_sort](
 [id][varchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,
 [use_value][decimal](18,2)NULL,
 [use_sort][int]NULL
)ON[PRIMARY]
GO

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>