- 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)
- --该片段来自于http://www.codesnippet.cn/detail/080420149257.html
来源: http://www.codesnippet.cn/detail/080420149257.html