我有一段SQL可以算出排行榜上高尔夫球手的排名。我对Spring还是很陌生,我不认为可以使用Spring JDBC执行此操作,因此我认为需要将其转换为mySQL存储过程。
谁能给我一些关于我需要转换的指针?简单地将CREATE PROCEDURE和参数列表放到这个地方是行不通的。
SET @rank = 1, @prev_val = NULL, @prev_rank = NULL;
SELECT rank FROM
(
SELECT @rank := IF(@prev_val!=winnings,@prev_rank+1,@rank) AS rank
, @prev_val := winnings AS winnings
, @prev_rank := @rank AS prevRank
, t.golferID
FROM
(
select g.golferID, sum(winnings) as winnings
from results r
join resultDetails rd on r.resultID = rd.resultID
join golfers g on rd.golferID = g.golferID
where r.status = 'C' and r.compID = 1
group by golferID order by winnings desc
) AS t
) AS showRank WHERE golferID = 16
最佳答案
这将使您入门:
drop procedure if exists get_golfer_rank;
delimiter #
create procedure get_golfer_rank
(
p_golferID int unsigned
)
proc_main:begin
set @rank = 0;
select
...
where golferID = p_golferID;
end proc_main #
delimiter ;
call get_golfer_rank(18);
关于java - 将SQL语句转换为mySQL存储过程,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/4502107/