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

MySQL存储过程获取记录排名(一般用于投票系统) 原理 计算当前学校的票数，暂且记作a 计算票数大于自己的学校数目，暂且记作b，如果b=0，则自己票数排名第一 计算票数小于自己的学校数目，暂且记作c，如果c=0，则自己票数排名最后 如果b!=0c!=0，那么b+c就是

MySQL存储过程获取记录排名(一般用于投票系统)

原理

计算当前学校的票数，暂且记作a

计算票数大于自己的学校数目，暂且记作b，如果b=0，则自己票数排名第一

计算票数小于自己的学校数目，暂且记作c，如果c=0，则自己票数排名最后

如果b != 0 && c != 0，那么b+c就是自己的票数排名

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 = (@gt+@lt);

SELECT @rank AS rank;

END IF;

END IF;

END;

call get_rank(1)

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网