mysql实现row_number
row_number() over(partition by col1 order by col2),作为oracle常用的分析函数,先分组后排序
mysql的写法:
select if(@uid=t.clo1 and @cid=t.clo2,@rank:=@rank+1,@rank:=1) as rank,t.*,@uid:=t.clo1,@cid:=t.clo2
from (select @uid:=null,@cid:=null,@rank:=0) r, (select clo1,clo2,city_name,clo3 from table_name order by clo1,clo2,clo3) t;
从表名为:table_name的表里取数据,根据字段clo1,clo2进行分组,根据字段clo3进行排序,rank是分组的行号,order by里一定要有两个分组字段及第三个排序字段。排完序只需要第一个后面就加’where rank=1’
套公式,try一try.