[Original] MySQL implements the ranking syntax like row_number over in Oracle or PostgreSQL

Both PostgreSQL and Oracle provide statements such as row_number () over () to rank corresponding fields, which is very convenient. MySQL does not provide such a syntax.

Recently, due to the increasing demand for migrating data from Oracle to MySQL, such conversion is inevitable. Below I will implement this in MySQL.

The table structure I provided this time is as follows,

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

I simulated 20 pieces of data for demonstration.

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)

In PostgreSQL, we will perform three different execution methods for such ranking functions:

First, complete ranking fields and sorting.

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)

Second, with complete ranking fields but not sorted.

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)

Third, there are no ranking fields and no sorting fields.

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 does not provide such a statement, so I use the following stored procedure.

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 ;

We also execute the first, second, and third queries. The results are as follows:

First,

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

Second,

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

Third,

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

This article is from "god, let's see it !" Blog, please be sure to keep this source http://yueliangdao0608.blog.51cto.com/397025/1350445