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 ;

我們同樣來執行第一種，第二種以及第三種查詢，結果如下：
第一種，

CALL sp_rownumber('t1','i_name','rank','desc');query resulti_namerankrownumCharlie131Charlie122Charlie123Charlie114Charlie105Lily71Lily72Lily73Lily64Lily65Lily56Lily47Lucy21Lucy22Lucy13Ytt151Ytt152Ytt143Ytt144Ytt145

第二種，

CALL sp_rownumber('t1','i_name',NULL,NULL);query resulti_namerankrownumCharlie121Charlie132Charlie113Charlie124Charlie105Lily71Lily72Lily43Lily64Lily75Lily66Lily57Lucy21Lucy12Lucy23Ytt141Ytt142Ytt143Ytt154Ytt155

第三種，

CALL sp_rownumber('t1',NULL,NULL,NULL);query resulti_namerankrownumLily71Lucy22Ytt143Ytt144Charlie125Charlie136Lily77Lily48Ytt149Lily610Lucy111Lily712Ytt1513Lily614Charlie1115Charlie1216Lucy217Charlie1018Lily519Ytt1520