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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.