The MySQL stored procedure obtains the ranking of records (generally used in the voting system) to calculate the number of votes of the current school. For the moment, the number of votes calculated by a is greater than the number of your own schools. For the moment, it is recorded as B. If b0, the number of votes in the first ranking is smaller than the number of schools in the current ranking. c is recorded for the moment. If c0 is used, the number of votes in the Last ranking is B! 0c! 0, then B + c is
The MySQL stored procedure obtains the ranking of records (generally used in the voting system) to calculate the number of votes of the current school. For the moment, the number of votes calculated by a is greater than the number of your own schools. For the moment, it is recorded as B, if B = 0, the number of votes in the first calculation is smaller than the number of votes in the school. For now, it is recorded as c. If c = 0, the number of votes in the last calculation is B! = 0c! = 0, then B + c is
MySQL Stored Procedure acquisition Record ranking (generally used in the voting system)
Principle
Calculate the number of votes of the current school.
The calculated number of votes is greater than the number of your own schools. For now, the number of votes is counted as B. If B = 0, the number of votes is ranked first.
The calculated number of votes is less than the number of your schools. For now, it is recorded as c. If c = 0, the number of votes is ranked last.
If B! = 0 & c! = 0, then B + c is the ranking of votes <无>
CREATE PROCEDURE `get_rank`(IN `school` INT)BEGINSET @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 = (SELECTCOUNT(*)FROM(SELECTtcl_works.school_id,COUNT(tcl_vote.vote_id) AS votesFROMtcl_worksLEFT JOIN tcl_vote ON tcl_vote.works_id = tcl_works.works_idGROUP BYtcl_works.school_idHAVINGvotes > @votesORDER BYvotes DESC)AS xxx);IF @gt = 0THENSET @rank = 1;SELECT @rank AS rank;ELSESET @lt = (SELECTCOUNT(*)FROM(SELECTtcl_works.school_id,COUNT(tcl_vote.vote_id) AS votesFROMtcl_worksLEFT JOIN tcl_vote ON tcl_vote.works_id = tcl_works.works_idGROUP BYtcl_works.school_idHAVINGvotes < @votesORDER BYvotes DESC)AS xxx);IF @lt = 0THENSET @rank =(SELECT COUNT(*) FROM tcl_school LIMIT 1);SELECT @rank AS rank;ELSESET @rank = (@gt+@lt);SELECT @rank AS rank;END IF;END IF;END;call get_rank(1)