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

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 ;