快乐学习
前程无忧、中华英才非你莫属!

MySQL 实现Oracle的row_number over 这样的排名函数

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

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

  1.                Table “ytt.t1”  
  2.  Column |         Type          | Modifiers   
  3. ——–+———————–+———–  
  4.  i_name | character varying(10) | not null  
  5.  rank   | integer               | not null  

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

  1. t_girl=# select * from t1 order by i_name;                               
  2.  i_name  | rank   
  3. ———+——  
  4.  Charlie |   12  
  5.  Charlie |   12  
  6.  Charlie |   13  
  7.  Charlie |   10  
  8.  Charlie |   11  
  9.  Lily       |    6  
  10.  Lily       |    7  
  11.  Lily        |    7  
  12.  Lily       |    6  
  13.  Lily       |    5  
  14.  Lily    |    7  
  15.  Lily    |    4  
  16.  Lucy    |    1  
  17.  Lucy    |    2  
  18.  Lucy    |    2  
  19.  Ytt     |   14  
  20.  Ytt     |   15  
  21.  Ytt     |   14  
  22.  Ytt     |   14  
  23.  Ytt     |   15  
  24. (20 rows)  

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

  1. t_girl=# select i_name,rank, row_number() over(partition by i_name order by rank descas rank_number from t1;     
  2.  i_name  | rank | rank_number   
  3. ———+——+————-  
  4.  Charlie     |   13 |           1  
  5.  Charlie    |   12 |           2  
  6.  Charlie    |   12 |           3  
  7.  Charlie    |   11 |           4  
  8.  Charlie    |   10 |           5  
  9.  Lily       |    7 |           1  
  10.  Lily       |    7 |           2  
  11.  Lily       |    7 |           3  
  12.  Lily       |    6 |           4  
  13.  Lily       |    6 |           5  
  14.  Lily       |    5 |           6  
  15.  Lily       |    4 |           7  
  16.  Lucy   |    2 |           1  
  17.  Lucy   |    2 |           2  
  18.  Lucy   |    1 |           3  
  19.  Ytt        |   15 |           1  
  20.  Ytt        |   15 |           2  
  21.  Ytt        |   14 |           3  
  22.  Ytt        |   14 |           4  
  23.  Ytt        |   14 |           5  
  24. (20 rows)  

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

 

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

 

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

 

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

 

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

  1. DELIMITER $$  
  2.   
  3.   
  4. USE `t_girl`$$  
  5.   
  6.   
  7. DROP PROCEDURE IF EXISTS `sp_rownumber`$$  
  8.   
  9.   
  10. CREATE  PROCEDURE `sp_rownumber`(  
  11.     IN f_table_name VARCHAR(64),  
  12.     IN f_column_partitionby VARCHAR(64),  
  13.     IN f_column_orderby VARCHAR(64),  
  14.     IN f_is_asc CHAR(4)  
  15.     )  
  16. BEGIN  
  17.       — Created by ytt at 2014/1/10  
  18.       — Do a row_number() over()   
  19.       DECLARE i INT;  
  20.       — Create a temporary table to save result.  
  21.       DROP TABLE IF EXISTS tmp_rownum;  
  22.       SET @stmt = CONCAT(‘create temporary table tmp_rownum select *,’‘rownum’‘ from ‘,f_table_name,‘ where 1 = 0’);  
  23.       PREPARE s1 FROM @stmt;  
  24.       EXECUTE s1;  
  25.         
  26.     
  27.       SET i = 0;  
  28.       SET @j = 0;  
  29.       SET @v_column_paritionby = ;  
  30.   
  31.   
  32.       — Check whether  parition column is null or not.  
  33.       IF (f_column_partitionby =  OR f_column_partitionby IS NULLTHEN   
  34.          — No additional parition column.  
  35.     SET @stmt = CONCAT(‘insert into tmp_rownum select *,@j:= @j+1 as rownum from ‘,  
  36.             f_table_name);      
  37.     PREPARE s1 FROM @stmt;  
  38.     EXECUTE s1;   
  39.       ELSE  
  40.        — Give partition column.  
  41.     SET @stmt = CONCAT(‘select count(*) from (select count(*) from ‘,f_table_name,‘ group by ‘,  
  42.             f_column_partitionby,‘) as a into @cnt’);  
  43.     PREPARE s1 FROM @stmt;  
  44.     EXECUTE s1;  
  45.         
  46.         WHILE i < @cnt  
  47.         DO          
  48.       — Get the partition value one by one.  
  49.       SET @stmt = CONCAT(‘select ‘,f_column_partitionby,‘ from ‘,f_table_name,‘ group by  ‘,f_column_partitionby,‘ limit ‘,i,‘,1 into @v_column_partitionby’);  
  50.       PREPARE s1 FROM @stmt;   
  51.       EXECUTE s1;  
  52.       — Check whether sort is needed.  
  53.           IF f_column_orderby =  OR f_column_orderby IS NULL THEN  
  54.             SET @stmt = CONCAT(‘insert into tmp_rownum select *,@j:= @j+1 as rownum from ‘,  
  55.             f_table_name,‘ where ‘,f_column_partitionby,‘ = ‘,@v_column_partitionby,);  
  56.       ELSE   
  57.         SET @stmt = CONCAT(‘insert into tmp_rownum select *,@j:= @j+1 as rownum from ‘,  
  58.             f_table_name,‘ where ‘,f_column_partitionby,‘ = ‘,@v_column_partitionby,‘  
  59.             order by ‘,f_column_orderby,’ ‘,f_is_asc);  
  60.           END IF;  
  61.           SET @j = 0;  
  62.       PREPARE s1 FROM @stmt;  
  63.       EXECUTE s1;   
  64.       
  65.           SET i = i + 1;  
  66.         END WHILE;  
  67.       END IF;  
  68.       — Reset all session variables.  
  69.       SET @j = NULL;  
  70.       SET @v_column_paritionby = NULL;  
  71.       SET @cnt = NULL;  
  72.       SELECT * FROM tmp_rownum;  
  73.     END$$  
  74.   
  75.   
  76. DELIMITER ;  

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

 

  1. CALL sp_rownumber(‘t1’,‘i_name’,‘rank’,‘desc’);  
  2. query result  
  3.   
  4.   
  5. i_name  rank    rownum  
  6. Charlie 13  1  
  7. Charlie 12  2  
  8. Charlie 12  3  
  9. Charlie 11  4  
  10. Charlie 10  5  
  11. Lily        7   1  
  12. Lily        7   2  
  13. Lily        7   3  
  14. Lily        6   4  
  15. Lily        6   5  
  16. Lily        5   6  
  17. Lily        4   7  
  18. Lucy    2   1  
  19. Lucy    2   2  
  20. Lucy    1   3  
  21. Ytt     15  1  
  22. Ytt     15  2  
  23. Ytt     14  3  
  24. Ytt     14  4  
  25. Ytt     14  5  

 

第二种,
 

 

  1. CALL sp_rownumber(‘t1’,‘i_name’,NULL,NULL);  
  2. query result  
  3.   
  4.   
  5. i_name  rank    rownum  
  6. Charlie 12  1  
  7. Charlie 13  2  
  8. Charlie 11  3  
  9. Charlie 12  4  
  10. Charlie 10  5  
  11. Lily        7   1  
  12. Lily        7   2  
  13. Lily        4   3  
  14. Lily        6   4  
  15. Lily        7   5  
  16. Lily        6   6  
  17. Lily        5   7  
  18. Lucy    2   1  
  19. Lucy    1   2  
  20. Lucy    2   3  
  21. Ytt     14  1  
  22. Ytt     14  2  
  23. Ytt     14  3  
  24. Ytt     15  4  
  25. Ytt     15  5  

 

第三种,
 

 

  1. CALL sp_rownumber(‘t1’,NULL,NULL,NULL);  
  2. query result  
  3.   
  4.   
  5. i_name  rank    rownum  
  6. Lily        7   1  
  7. Lucy    2   2  
  8. Ytt     14  3  
  9. Ytt     14  4  
  10. Charlie 12  5  
  11. Charlie 13  6  
  12. Lily        7   7  
  13. Lily        4   8  
  14. Ytt     14  9  
  15. Lily        6   10  
  16. Lucy    1   11  
  17. Lily        7   12  
  18. Ytt     15  13  
  19. Lily        6   14  
  20. Charlie 11  15  
  21. Charlie 12  16  
  22. Lucy    2   17  
  23. Charlie 10  18  
  24. Lily        5   19  
  25. Ytt     15  20  
打赏

未经允许不得转载:同乐学堂 » MySQL 实现Oracle的row_number over 这样的排名函数

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!