MYSQL 存储过程实现类似ORACLE row_number 和 rank 的简单例子

最新推荐文章于 2021-02-28 14:15:40 发布

bczzm

最新推荐文章于 2021-02-28 14:15:40 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： MYSQL 文章标签： oracle mysql 存储

本文链接：https://blog.youkuaiyun.com/bczzm/article/details/51505840

MYSQL 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种使用MySQL存储过程来实现row_number和rank排名函数的方法。通过定义存储过程，可以为指定的数据表按课程ID和分数进行排序，并为每个记录分配一个排名。此方法适用于需要在数据库级别进行排名计算的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

row_number 实现：

DELIMITER $$


USE `zhangzm`$$


DROP PROCEDURE IF EXISTS `row_number`$$


CREATE DEFINER=`root`@`%` PROCEDURE `row_number`()
BEGIN
  DECLARE rn INT DEFAULT 0 ;
  DECLARE _StuId VARCHAR (5) ;
  DECLARE _CourseId VARCHAR (3) ;
  DECLARE _Score FLOAT ;
  DECLARE last_CourseId VARCHAR (3) ;
  DECLARE done TINYINT DEFAULT FALSE ;
  DECLARE cur CURSOR FOR 
  SELECT 
    `StuId`,
    `CourseId`,
    `Score` 
  FROM
    `zhangzm`.`tblScore` 
  ORDER BY `CourseId`,
    `Score` DESC ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
  DROP TABLE IF EXISTS temp_tblScore ;
  CREATE TEMPORARY TABLE temp_tblScore LIKE `zhangzm`.`tblScore` ;
  ALTER TABLE temp_tblScore 
    ADD COLUMN row_number INT UNSIGNED NOT NULL ;
  OPEN cur ;
  WHILE
    done <=> FALSE DO FETCH cur INTO _StuId,
    _CourseId,
    _Score ;
    IF _CourseId <> last_CourseId 
    THEN SET rn = 1 ;
    ELSE SET rn = rn + 1 ;
    END IF ;
    IF rn < 4 
    THEN 
    INSERT INTO temp_tblScore 
    SELECT 
      _StuId,
      _CourseId,
      _Score,
      rn ;
    END IF ;
    SET last_CourseId = _CourseId ;
  END WHILE ;
  SELECT 
    * 
  FROM
    temp_tblScore ;
  CLOSE cur ;
  DROP TABLE IF EXISTS temp_tblScore ;
END$$


DELIMITER ;

rank实现：

DELIMITER $$


USE `zhangzm`$$


DROP PROCEDURE IF EXISTS `rank`$$


CREATE DEFINER=`root`@`%` PROCEDURE `rank`()
BEGIN
  DECLARE rn INT DEFAULT 1 ;
  DECLARE _StuId VARCHAR (5) ;
  DECLARE _CourseId VARCHAR (3) ;
  DECLARE _Score FLOAT ;
  DECLARE last_CourseId VARCHAR (3) ;
  DECLARE last_Score FLOAT ;
  DECLARE done TINYINT DEFAULT FALSE ;
  DECLARE cur CURSOR FOR 
  SELECT 
    `StuId`,
    `CourseId`,
    `Score` 
  FROM
    `zhangzm`.`tblScore` 
  ORDER BY `CourseId`,
    `Score` DESC ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
  DROP TABLE IF EXISTS temp_tblScore ;
  CREATE TEMPORARY TABLE temp_tblScore LIKE `zhangzm`.`tblScore` ;
  ALTER TABLE temp_tblScore 
    ADD COLUMN row_number INT UNSIGNED NOT NULL ;
  OPEN cur ;
  WHILE
    done <=> FALSE DO FETCH cur INTO _StuId,
    _CourseId,
    _Score ;
    IF _CourseId <> last_CourseId 
    THEN SET rn = 1 ;
    ELSEIF _Score <> last_Score 
    THEN SET rn = rn + 1 ;
    END IF ;
    IF rn < 4 
    THEN 
    INSERT INTO temp_tblScore 
    SELECT 
      _StuId,
      _CourseId,
      _Score,
      rn ;
    END IF ;
    SET last_CourseId = _CourseId ;
    SET last_Score = _Score ;
  END WHILE ;
  SELECT 
    * 
  FROM
    temp_tblScore ;
  CLOSE cur ;
  DROP TABLE IF EXISTS temp_tblScore ;
END$$


DELIMITER ;