mysql获取rownum

表结构

CREATE TABLE `test` (
  `field1` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `field2` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `field3` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

获取行号

select @rownum := @rownum + 1 rownum,t.* from test t,(select @rownum := 0 ) r

分组获取组内排行

select field2,field1,field3,if(@p=field2,@r := @r+1,@r := 1) as rank1,( @p := field2 ) as p from test t,(select @p := null, @r := 1) a 

mysql8.0 rank

select field2,rank() over (order by field2) r from test