MySQL计算排名并更新原表

Question

MySQL server 5.6.20(目前最新版本)

给定按日期表的价格。我添加了一个新列“排名”，它表示按日期对商品价格的排名。

Date      Item  Price  Rank
1/1/2014  A     5.01   0  
1/1/2014  B     31     0  
1/1/2014  C     1.5    0  
1/2/2014  A     5.11   0  
1/2/2014  B     20     0  
1/2/2014  C     5.5    0  
1/3/2014  A     30     0  
1/3/2014  B     11.01  0  
1/3/2014  C     22     0  

如何写SQL语句计算排名并更新原表？下面是填充排名的预期表格。排名计算按日期分组(1/1、1/2、1/3 等)。

Date      Item  Price  Rank
1/1/2014  A     5.01   2  
1/1/2014  B     31     1  
1/1/2014  C     1.5    3  
1/2/2014  A     5.11   3  
1/2/2014  B     20     1  
1/2/2014  C     5.5    2  
1/3/2014  A     30     1  
1/3/2014  B     11.01  3  
1/3/2014  C     22     2  

此外，如果几件商品的价格相同，MySQL 将如何处理排名？例如:

Date      Item  Price  Rank
1/4/2014  A     31     0  
1/4/2014  B     31     0  
1/4/2014  C     1.5    0  

谢谢。

Answer 1

您可以使用变量在查询中获得排名:

  select t.*,
         (@rn := if(@d = date, @rn + 1,
                    if(@d := date, 1, 1)
                   )
         ) as rank
  from pricebydate t cross join
       (select @d := NULL, @rn := 0) vars
  order by date, price desc;

您可以使用 join 将其放入 update 中:

update pricebydate pbd join
       (select t.*,
             (@rn := if(@d = date, @rn + 1,
                        if(@d := date, 1, 1)
                       )
             ) as rank
        from pricebydate t cross join
             (select @d := NULL, @rn := 0) vars
        order by date, price desc
       ) r
       on pbd.date = r.date and pbd.item = item
    set pbd.rank = r.rank;