每个id的MySQL编号日期

Question

我的问题可能很容易回答(很抱歉)，但我找不到解决办法。

我有一张这样的桌子：

id / date  
1 / 2013-5-5 13:44:12  
1 / 2013-5-5 15:34:19  
1 / 2013-6-5 05:14:07  
2 / 2012-3-4 06:33:33  
2 / 2013-5-5 12:23:10  
3 / 2012-5-7 11:43:17   

我想要的是：

id / date / position  
1 / 2013-5-5 13:44:12 / 1    
1 / 2013-5-5 15:34:19 / 2  
1 / 2013-6-5 05:14:07 / 3   
2 / 2012-3-4 06:33:33 / 1   
2 / 2013-5-5 12:23:10 / 2   
3 / 2012-5-7 11:43:17 / 1  

因此，每个id的最早日期应该是位置1，第二位，最早的2等等。如何在MySQL中创建职位列？

非常感谢!

Answer 1

不幸的是，MySQL没有为数据分配行号的窗口函数。但是有几种方法可以获得结果，您可以使用类似于以下的子查询返回这个职位编号：

select t.id,
  t.date,
  (select count(*)
   from yourtable r
   where r.id = t.id
     and r.date <= t.date) position
from yourtable t
order by t.id, t.date;

见与Demo。

还可以实现用户定义的变量：

select id, date, position
from
(
  select t.id,
    t.date,
    @row:=case 
            when @prev=t.id and @pd<= t.date
            then @row else 0 end +1 position,
    @prev:=t.id,
    @pd:=t.date
  from yourtable t
  cross join (select @row:=0, @prev:=0, @pd:=null) c
  order by t.id, t.date
)d

请参阅与Demo

Answer 2

你可以使用会话变量，但我很感情用事；我喜欢慢慢来的老式方法.

 SELECT x.*
      , COUNT(*) rank 
   FROM my_table x 
   JOIN my_table y 
     ON y.id = x.id 
    AND y.date <= x.date 
  GROUP 
     BY id,date;

Answer 3

计算每个日期以下的行数：

UPDATE
    `table`
SET
    position = 
    (
        SELECT
            COUNT(1) + 1
        FROM
            `table` t
        WHERE
            t.`date` < `table`.`date`
            AND t.id = table.id
        ORDER BY
            `date`
    )

SQL Fiddle (只选择可用，但它是相同的)