mysql - 更新是否基于时间差异的 id

Question

我有一个下表

std_id intra_id number startime           
1        245     18970  20170101 10:30:31 
2        245     18970  20170101 10:40:00 
3        245     18970  20170101 10:50:12 
4        789      6586  20170101 12:34:45 
5        754     346456 20170101 10:23:45 
6        4332    234567 20170101 10:13:40 
7        4332    234567 20170101 10:26:46 

连接表

 intra_id number endimeime
   245     18970  20170101 10:29:31
   789      6586  20170101 12:34:45
   754     346456 20170101 10:23:45
   4332    234567 20170101 10:12:30

预期输出:

std_id intra_id number startime            diff
    1   245   18970   2017-01-01 10:30:31   60
    2         18970   2017-01-01 10:40:00   629
    3         18970   2017-01-01 10:50:12   1241
    4   789   6586    2017-01-01 12:34:45   0
    5   754   346456  2017-01-01 10:23:45   0
    6   4332  234567  2017-01-01 10:13:40   70
    7         234567  2017-01-01 10:26:46   856

我通过获取 i/p 的开始时间和连接表的结束时间之间的时间差来尝试此操作。但它没有根据我的预期输出进行更新。

因此，当我的intra_id计数> 1表示相同的数字但不同的std_id时，它必须具有单个intra_id作为具有较少差异时间的数字

Answer 1

您可以使用以下查询:

SELECT std_id, IF(grp = 1, intra_id, '') AS intra_id,
       number, startime, diff
FROM (       
   SELECT std_id, 
          intra_id,
          @grp := IF(intra_id = @id, @grp + 1,
                     IF(@id := intra_id, 1, 1)) AS grp,  
          number, startime, diff
   FROM (
      SELECT t1.std_id, t1.intra_id, t1.number, t1.startime,
             TIME_TO_SEC(TIMEDIFF(t1.startime, t2.endtime)) AS diff
      FROM table1 AS t1
      JOIN table2 AS t2 ON t1.intra_id = t2.intra_id) AS t   
   CROSS JOIN (SELECT @id := 0) AS v   
   ORDER BY t.intra_id, diff) AS x;

查询使用变量来实现intra_id“不显示”效果，尽管这最好由应用程序的表示逻辑处理。

Demo here