SELECT MAX(score),MIN(score) INTO :ll_maxscore,:ll_minscore FROM exams WHERE sbt ='001";
k = ll_maxscore - ll_minscore + 1
i = 1
FOR j = 1 TO k
UPDATE exams SET crd = :i WHERE score = :ll_maxscore + 1 - :j AND sbt ='001';
IF SQLCA.SQLNRows > 0 THEN
i = i+SQLCA.SQLNRows
END IF
NEXT
怎么改在SQL存盤程序里實作這個功能呢?
謝
uj5u.com熱心網友回復：
create procedure sp_pro1 @sbt varchar(20)
as
declare @ll_maxscore int,@ll_minscore int,@k int,@j int,@i int
SELECT @ll_maxscore = MAX(score),@ll_minscore = MIN(score) FROM exams WHERE sbt = @sbt
if @@rowcount = 0 return -1
select @k = @ll_maxscore - @ll_minscore + 1,@i = 1,@j = 1
while @j <= @k
begin
UPDATE exams SET crd = @i WHERE score = @ll_maxscore + 1 - @j AND sbt = @sbt
select @i = @i + @@rowcount,@j = @j + 1
if @@error <> 0 return -1
end
return 0
uj5u.com熱心網友回復：
如果不涉及到界面處理，都可以改的uj5u.com熱心網友回復：
首先謝謝樓上的回答還有個條件沒有利用到
IF SQLCA.SQLNRows > 0 THEN
i = i+SQLCA.SQLNRows
END IF
這個在存盤程序,怎么替換呢?
UPDATE exams SET crd = @i WHERE score = @ll_maxscore + 1 - @j AND sbt = @sbt
select @i = @i + @@rowcount,@j = @j + 1
這樣沒有按順序排出來啊,我是想實作排名功能
uj5u.com熱心網友回復：
create procedure sp_pro1 @sbt varchar(20)as
create table #sbt001 (score decimal(18,2) null default 0,
id decimal(18,0) identity(1,1) not null)
insert #sbt001(score)
select distinct score from exams where sbt=@sbt order by score desc
update exams set crd=#sbt001.id from exams,#sbt001 where exams.score=#sbt001.score
return 0
--散分
uj5u.com熱心網友回復：
關注 接分轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116484.html
標籤：數據庫相關
上一篇：關于PB8.0動態執行SP的問題