--排列名次的sql server函數-------------（原創）
2024-08-31 00:48:14
供稿：網友
--排列位置的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 '[email protected]_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
