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

第二种，

query result

第三种，

query result

本文出自 “天主，咱们不睹不集！” 博客，请务必保存此出处http://yueliangdao0608.blog.51cto.com/397025/1350445