Mysql实现RowNumber

参考：http://www.uncletoo.com/html/mysql/1060.html

为每组记录添加行号

• 设置一个变量SET @row_number = 0;
• 在mysql中每次都累加
  代码如下

SET  @row_number = 0;
 SELECT
    (@row_number:=@row_number + 1)  AS  num, firstName, lastName
 FROM
    employees
 LIMIT  5;

为每个分组的数据都打上连续的id

• 设置一个变量记录row_number
• 当前一个字段当前的customerNumber(customer_last)和上一个customerNumber 相等时row_number+1，否则row_number=1
• 用一个customer_last变量记录上一个customerNumber
  代码如下

set @row_number := 1;
SELECT
    @row_number := CASE
         WHEN  @customer_last = customerNumber  THEN  @row_number + 1
         ELSE  1
     END   AS  num,
    @customer_last := customerNumber  as  CustomerNumber,
    paymentDate,
    amount
 FROM
    payments
 ORDER BY  customerNumber;