【原創】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 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

本文出自 “上帝，咱們不見不散！” 部落格，請務必保留此出處http://yueliangdao0608.blog.51cto.com/397025/1350445