低版本mysql 实现row_number() ,rank() ,dense_rank()及其窗口函数

测试表

 create table  test (id int(4) ,  name VARCHAR(10) )

• row_number() 实现

SELECT t.name , t.id, (@cur_rank := @cur_rank + 1) ranking
FROM  test t , (SELECT @cur_rank := 0) r
ORDER BY id  DESC;

• ROW_NUMBER() over(partiton by name ORDER BY id DESC) 实现 @pre_name
  := t.name 要保留

 select  t.name, t.id,
IF(@pre_name = t.name, @cur_rank := @cur_rank + 1, @cur_rank := 1) ranking,
@pre_name := t.name
FROM test t, (SELECT @cur_rank := 0, @pre_name := NULL) r
ORDER BY name, id DESC; 

• rank() 实现

SELECT t.id,
@rank_counter := @rank_counter + 1