【原创】MySQL 实现Oracle或者PostgreSQL的row_number over 这样的排名语法

PostgreSQL 和Oracle 都提供了 row_number() over() 这样的语句来进行对应的字段排名， 很是方便。  MySQL却没有提供这样的语法。 
最近由于从Oracle 迁移到MySQL的需求越来越多，所以这样的转化在所难免。 下面我在MySQL里面来实现这样的做法。


这次我提供的表结构如下，

Table "ytt.t1"
 Column | Type | Modifiers
--------+-----------------------+-----------
 i_name | character varying(10) | not null
 rank | integer | not null

我模拟了20条数据来做演示。

t_girl=# select * from t1 order by i_name;
 i_name | rank
---------+------
 Charlie | 12
 Charlie | 12
 Charlie | 13
 Charlie | 10
 Charlie | 11
 Lily     | 6
 Lily     | 7
 Lily     | 7
 Lily     | 6
 Lily     | 5
 Lily | 7
 Lily | 4
 Lucy | 1
 Lucy | 2
 Lucy | 2
 Ytt | 14
 Ytt | 15
 Ytt | 14
 Ytt | 14
 Ytt | 15
(20 rows)

在PostgreSQL下，我们来对这样的排名函数进行三种不同的执行方式1：
第一种，完整的带有排名字段以及排序。

t_girl=# select i_name,rank, row_number() over(partition by i_name order by rank desc) as rank_number from t1;
 i_name | rank | rank_number
---------+------+-------------
 Charlie     | 13 | 1
 Charlie    | 12 | 2
 Charlie    | 12 | 3
 Charlie    | 11 | 4
 Charlie    | 10 | 5
 Lily     | 7 | 1
 Lily     | 7 | 2
 Lily     | 7 | 3
 Lily     | 6 | 4
 Lily     | 6 | 5
 Lily     | 5 | 6
 Lily     | 4 | 7
 Lucy    | 2 | 1
 Lucy    | 2 | 2
 Lucy    | 1 | 3
 Ytt     | 15 | 1
 Ytt     | 15 | 2
 Ytt     | 14 | 3
 Ytt     | 14 | 4
 Ytt     | 14 | 5
(20 rows)

第二种，带有完整的排名字段但是没有排序。

t_girl=# select i_name,rank, row_number() over(partition by i_name ) as rank_number from t1;
 i_name | rank | rank_number
---------+------+-------------
 Charlie     | 12 | 1
 Charlie    | 12 | 2
 Charlie    | 13 | 3
 Charlie    | 10 | 4
 Charlie    | 11 | 5
 Lily     | 6 | 1
 Lily     | 7 | 2
 Lily     | 7 | 3
 Lily     | 6 | 4
 Lily     | 5 | 5
 Lily     | 7 | 6
 Lily     | 4 | 7
 Lucy    | 1 | 1
 Lucy    | 2 | 2
 Lucy    | 2 | 3
 Ytt     | 14 | 1
 Ytt     | 15 | 2
 Ytt     | 14 | 3
 Ytt     | 14 | 4
 Ytt     | 15 | 5
(20 rows)

第三种， 没有任何排名字段，也没有任何排序字段。

t_girl=# select i_name,rank, row_number() over() as rank_number from t1;
 i_name | rank | rank_number
---------+------+-------------
 Lily     | 7 | 1
 Lucy    | 2 | 2
 Ytt     | 14 | 3
 Ytt     | 14 | 4
 Charlie    | 12 | 5
 Charlie    | 13 | 6
 Lily     | 7 | 7
 Lily     | 4 | 8
 Ytt     | 14 | 9
 Lily     | 6 | 10
 Lucy    | 1 | 11
 Lily     | 7 | 12
 Ytt     | 15 | 13
 Lily     | 6 | 14
 Charlie    | 11 | 15
 Charlie    | 12 | 16
 Lucy    | 2 | 17
 Charlie    | 10 | 18
 Lily     | 5 | 19
 Ytt     | 15 | 20
(20 rows)

MySQL 没有提供这样的语句，所以我用了以下的存储过程来实现。

DELIMITER $$


USE `t_girl`$$


DROP PROCEDURE IF EXISTS `sp_rownumber`$$


CREATE PROCEDURE `sp_rownumber`(
    IN f_table_name VARCHAR(64),
    IN f_column_partitionby VARCHAR(64),
    IN f_column_orderby VARCHAR(64),
    IN f_is_asc CHAR(4)
    )
BEGIN
      -- Created by ytt at 2014/1/10
      -- Do a row_number() over()
      DECLARE i INT;
      -- Create a temporary table to save result.
      DROP TABLE IF EXISTS tmp_rownum;
      SET @stmt = CONCAT('create temporary table tmp_rownum select *,''rownum'' from ',f_table_name,' where 1 = 0');
      PREPARE s1 FROM @stmt;
      EXECUTE s1;


      SET i = 0;
      SET @j = 0;
      SET @v_column_paritionby = '';


      -- Check whether  parition column is null or not.
      IF (f_column_partitionby = '' OR f_column_partitionby IS NULL) THEN
         -- No additional parition column.
SET @stmt = CONCAT('insert into tmp_rownum select *,@j:= @j+1 as rownum from ',
f_table_name);
PREPARE s1 FROM @stmt;
EXECUTE s1;
      ELSE
       -- Give partition column.
SET @stmt = CONCAT('select count(*) from (select count(*) from ',f_table_name,' group by ',
f_column_partitionby,') as a into @cnt');
PREPARE s1 FROM @stmt;
EXECUTE s1;

        WHILE i < @cnt
        DO
 -- Get the partition value one by one.
 SET @stmt = CONCAT('select ',f_column_partitionby,' from ',f_table_name,' group by  ',f_column_partitionby,' limit ',i,',1 into @v_column_partitionby');
 PREPARE s1 FROM @stmt;
 EXECUTE s1;
 -- Check whether sort is needed.
          IF f_column_orderby = '' OR f_column_orderby IS NULL THEN
            SET @stmt = CONCAT('insert into tmp_rownum select *,@j:= @j+1 as rownum from ',
f_table_name,' where ',f_column_partitionby,' = ''',@v_column_partitionby,'''');
 ELSE
   SET @stmt = CONCAT('insert into tmp_rownum select *,@j:= @j+1 as rownum from ',
f_table_name,' where ',f_column_partitionby,' = ''',@v_column_partitionby,'''
order by ',f_column_orderby,' ',f_is_asc);
          END IF;
          SET @j = 0;
 PREPARE s1 FROM @stmt;
 EXECUTE s1;

          SET i = i + 1;
        END WHILE;
      END IF;
      -- Reset all session variables.
      SET @j = NULL;
      SET @v_column_paritionby = NULL;
      SET @cnt = NULL;
      SELECT * FROM tmp_rownum;
    END$$


DELIMITER ;

我们同样来执行第一种，第二种以及第三种查询，结果如下：
第一种，

query result

i_name	rank	rownum
Charlie	13	1
Charlie	12	2
Charlie	12	3
Charlie	11	4
Charlie	10	5
Lily	7	1
Lily	7	2
Lily	7	3
Lily	6	4
Lily	6	5
Lily	5	6
Lily	4	7
Lucy	2	1
Lucy	2	2
Lucy	1	3
Ytt	15	1
Ytt	15	2
Ytt	14	3
Ytt	14	4
Ytt	14	5

第二种，

query result

i_name	rank	rownum
Charlie	12	1
Charlie	13	2
Charlie	11	3
Charlie	12	4
Charlie	10	5
Lily	7	1
Lily	7	2
Lily	4	3
Lily	6	4
Lily	7	5
Lily	6	6
Lily	5	7
Lucy	2	1
Lucy	1	2
Lucy	2	3
Ytt	14	1
Ytt	14	2
Ytt	14	3
Ytt	15	4
Ytt	15	5

第三种，

query result

i_name	rank	rownum
Lily	7	1
Lucy	2	2
Ytt	14	3
Ytt	14	4
Charlie	12	5
Charlie	13	6
Lily	7	7
Lily	4	8
Ytt	14	9
Lily	6	10
Lucy	1	11
Lily	7	12
Ytt	15	13
Lily	6	14
Charlie	11	15
Charlie	12	16
Lucy	2	17
Charlie	10	18
Lily	5	19
Ytt	15	20