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;