update if the id based on difference in time, MySQL

update if the id based on difference in time

drop table if exists table1;
drop table if exists table2;

CREATE TABLE table1
    (`std_id` int, `intra_id` int, `number` int, `startime` datetime)
;
    
INSERT INTO table1
    (`std_id`, `intra_id`, `number`, `startime`)
VALUES
    (1, 245, 18970, '2017-01-01 10:30:31'),
    (2, 245, 18970, '2017-01-01 10:40:00'),
    (3, 245, 18970, '2017-01-01 10:50:12'),
    (4, 789, 6586, '2017-01-01 12:34:45'),
    (5, 754, 346456, '2017-01-01 10:23:45'),
    (6, 4332, 234567, '2017-01-01 10:13:40'),
    (7, 4332, 234567, '2017-01-01 10:26:46')
;

CREATE TABLE table2
    (`intra_id` int, `number` int, `endtime` datetime)
;
    
INSERT INTO table2
    (`intra_id`, `number`, `endtime`)
VALUES
    (245, 18970, '2017-01-01 10:29:31'),
    (789, 6586, '2017-01-01 12:33:45'),
    (754, 346456, '2017-01-01 10:22:00'),
    (4332, 234567, '2017-01-01 10:12:30')
;

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;

drop table if exists table1;
drop table if exists table2;


run \| edit \| history \| help	0