1、原始数据：SELECT * FROM test_rownumber;

 

 

 

2、ROW_NUMBER功能实现SQL

SELECT * FROM
(
SELECT
-- 当变量@name等于字段值的时候，变量@rn加1，如果不相等赋值为 1
@rn := CASE WHEN @name = NAME THEN @rn + 1 ELSE 1 END AS rn ,
-- 把name字段值赋值于变量@name
@name:=name as name1 ,
num
FROM
( SELECT * FROM test_rownumber ORDER BY name,num DESC ) a,
-- 初始化一个变量值
(SELECT @rn = 0,@name=0) b
) c