MySQL_Implementing intra-group sorting-the function of rank() function in Oracle

 Suppose the table is student, and the data is as follows:

 We want to implement the rank() function in Oracle in MySQL, that is, sorting within the group, specifically: It is to rank the students (name) in the student table according to the course (course) and according to the score (score).

 First create a new stored procedure realize_rank_in_MySQL, the code is as follows: # ##

DROP PROCEDURE IF EXISTS realize_rank_in_MySQL;
DELIMITER ;;
CREATE PROCEDURE realize_rank_in_MySQL()
BEGIN
    DECLARE i int;
    SET i = 0;
    WHILE i < (select count(DISTINCT course) from student) DO
            SET @ROW =0;
            INSERT INTO student_rank
            SELECT *, (@ROW:=@ROW+1) AS rank FROM student
            WHERE course=(select DISTINCT course from student limit i,1)
            ORDER BY score DESC;
            set i = i + 1;
    END WHILE;
END
;;
DELIMITER ;

Then enter the following query statement:

drop table if exists student_rank;
create table student_rank like student;
alter table student_rank add rank int;
call realize_rank_in_MySQL;
select * from student_rank;

 The result is as follows:

Note: The ranking here does not realize the situation where the ranking is the same when the scores are the same.

Related articles:

MySQL’s rank function implementation_MySQL

Using standard sql in hive to implement intra-group sorting

Related videos:

Shangguan Oracle Getting Started to Mastery Video Tutorial

The above is the detailed content of MySQL_Implementing intra-group sorting-the function of rank() function in Oracle. For more information, please follow other related articles on the PHP Chinese website!