mysql - 根据时间戳更新类似行

Question

我有几行想要根据时间戳和用户 ID 等进行更新。
所以我的数据看起来像这样

| INT(11) | TIMESTAMP        | VARCHAR(50) |
| user id | timestamp        | like id     |  
| 1       | 10/20/2014 10:30 |             |
| 1       | 10/20/2014 9:45  |             |  
| 1       | 10/20/2014 22:30 |             |
| 2       | 10/20/2014 9:45  |             |  
| 3       | 10/20/2014 10:30 |             |
| 3       | 10/20/2014 9:45  |             |  

我希望它像这样更新，其中具有相同用户 ID 和时间戳的行，如果一小时(同一天)内任何其他行中具有相同用户 ID 的另一个条目，则时间戳匹配。

| user id | timestamp        | like id |  
| 1       | 10/20/2014 10:30 | ASDF-1  |
| 1       | 10/20/2014 9:45  | ASDF-1  |
| 1       | 10/20/2014 11:30 | ASDF-1  |
| 1       | 10/20/2014 21:45 | ADGG-2  |   
| 1       | 10/20/2014 22:30 | ADGG-2  |
| 2       | 10/20/2014 9:45  | GFDQ-3  |  
| 3       | 10/20/2014 10:30 | QWER-4  |
| 3       | 10/20/2014 9:45  | QWER-4  |  

这可以在 MYSQL 更新语句中完成吗？我还没想到办法。

编辑:我还想为每个分组的 like_ids 生成一个随机的 50 个字符的字符串

Answer 1

这是一个返回您想要的结果的 SQL 查询:

select t.userid, t.timestamp,
       (@rn := if(@u = userid and @ts <= timestamp + interval 1 hour,
                  if(@ts := timestamp, @rn, @rn),
                  if(@u := userid, if(@ts := timestamp, @rn + 1, @rn + 1), @rn + 1)
                 )
       ) as likeid
from table t cross join
     (select @rn := 0, @u := 0, @ts := 0) vars
order by userid, timestamp;

所有带有嵌套 if 的奇怪语法都是将变量的赋值放入单个语句中。

然后您可以使用它进行更新:

update t join
       (select t.userid, t.timestamp,
               (@rn := if(@u = userid and @ts <= timestamp + interval 1 hour,
                          if(@ts := timestamp, @rn, @rn),
                          if(@u := userid, if(@ts := timestamp, @rn + 1, @rn + 1), @rn + 1)
                         )
               ) as likeid
        from table t cross join
             (select @rn := 0, @u := 0, @ts := 0) vars
        order by userid, timestamp
       ) tnew
       on t.userid = tnew.userid and t.timestamp = tnew.timestamp
    set t.likeid = tnew.likeid;