mysql - 在带有分区的mysql中进行排名顺序/十分位

Question

我的数据集中有多个城市组，我正在尝试对 mysql 中每个组的订单价格进行排名。有人可以帮我将分区子句转换为mysql吗？

Answer 1

我假设您正在寻找与 rank() over (partition by city order byprice) 等效的内容。您可以使用子查询来完成此操作:

select d.*,
       (select 1 + count(price)
        from dataset d2
        where d2.city = d.city and d2.price < d.price
       ) as rank
from dataset d;

或者使用变量:

select d.*,
       (@rn := if(@city = city and @price > price, if(@price = @price, @rn + 1, @rn + 1),
                  if(@city = city and @price = price, @rn,
                     if(@city := city, if(@price := price, 1, 1), 1)
                    )
                 )
       ) as rank                  
from dataset d cross join
     (select @rn := 0, @city := '', @price = -1)
order by city, price;