Mysql实现Oracle中row_number ( ) over ( partition BY ORDER BY DESC )

Mysql实现Oracle中row_number ( ) over ( partition BY 字段1 ORDER BY 字段2 DESC )

 

例表：

 

案例1，row_number ( ) over (ORDER BY 字段2 DESC )

Oracle	Mysql
SELECT        b.*,        row_number ( ) over ( ORDER BY PRICE DESC ) AS rk FROM        TEST b;	SELECT        b.*,        ( @rownum := @rownum + 1 ) rk FROM        ( SELECT @rownum := 0 ) rownum ,        TEST b ORDER BY        PRICE DESC;

 

案例2，row_number ( ) over ( partition BY 字段1 ORDER BY 字段2 DESC )

Oracle	Mysql
SELECT        b.foodname,        b.price,     b.orderid,        row_number ( ) over ( partition by ORDERID ORDER BY PRICE DESC ) rk FROM        TEST b;	SELECT        b.foodname,        b.price, IF        ( @p = orderid, @rownum := @rownum + 1, @rownum := 1 ) rk,        @p := orderid orderid FROM        ( SELECT @p := NULL, @rownum := 0 ) rownum,        TEST b ORDER BY        orderid,        PRICE DESC; 备注：注意标红处是否遗漏

 

案例3，row_number ( ) over ( partition BY 字段1，字段3 ORDER BY 字段2 DESC )

Oracle	Mysql
SELECT        b.*,        rank ( ) over ( partition BY ORDERID, FOODNAME ORDER BY PRICE DESC ) AS rk FROM        TEST b;	SELECT        b.price, IF        ( @p = orderid AND @q = foodname, @rownum := @rownum + 1, @rownum := 1 ) rk,        @p := orderid orderid,        @q := foodname foodname FROM        ( SELECT @p := NULL, @q := NULL, @rownum := 0 ) rownum,        TEST b ORDER BY        orderid,        foodname,        price DESC;