存储过程实现开窗函数功能

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