mysql 投票总排行_MySQL存储过程获取记录排名（一般用于投票系统）

下面是编程之家 jb51.cc 通过网络收集整理的代码片段。

编程之家小编现在分享给大家，也给大家做个参考。

CREATE PROCEDURE `get_rank`(IN `school` INT)

BEGIN

SET @votes = (SELECT COUNT(tcl_vote.vote_id) AS counts FROM tcl_works LEFT JOIN tcl_vote ON tcl_vote.works_id = tcl_works.works_id WHERE tcl_works.school_id=school);

SET @gt = (

SELECT

COUNT(*)

FROM

(

SELECT

tcl_works.school_id,COUNT(tcl_vote.vote_id) AS votes

FROM

tcl_works

LEFT JOIN tcl_vote ON tcl_vote.works_id = tcl_works.works_id

GROUP BY

tcl_works.school_id

HAVING

votes > @votes

ORDER BY

votes DESC

)

AS xxx

);

IF @gt = 0

THEN

SET @rank = 1;

SELECT @rank AS rank;

ELSE

SET @lt = (

SELECT

COUNT(*)

FROM

(

SELECT

tcl_works.school_id,COUNT(tcl_vote.vote_id) AS votes

FROM

tcl_works

LEFT JOIN tcl_vote ON tcl_vote.works_id = tcl_works.works_id

GROUP BY

tcl_works.school_id

HAVING

votes < @votes

ORDER BY

votes DESC

)

AS xxx

);

IF @lt = 0

THEN

SET @rank =(SELECT COUNT(*) FROM tcl_school LIMIT 1);

SELECT @rank AS rank;

ELSE

SET @rank = (@[email protected]);

SELECT @rank AS rank;

END IF;

END IF;

END;

call get_rank(1)

以上是编程之家(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。