计算排名和更新原始表的MySQL

Question

MySQL服务器5.6.20 (目前最新版本)

按日期表计算价格。我添加了一个新列"Rank"，它表示按日期对商品价格的排名。

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;

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

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;

Answer 2

我相信这能做你想做的事：

Update YourTable As T1                                   
Set    ItemRank = (                                               
    Select ItemRank From (                                         
        Select Rank() Over (Partition By ItemDate Order By Price Desc) 
        As ItemRank, Item, ItemDate                                    
        From YourTable                                            
    ) As T2                                                        
    Where T2.Item = T1.Item                                        
    And   T2.ItemDate = T1.ItemDate                                
)                                                              

重复的职级将被处理为拥有同等的级别。