Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
update if the id based on difference in time
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
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;
absolute service time: 0,43 sec
edit mode
|
history
|
discussion
std_id
intra_id
number
startime
diff
1
1
245
18970
01.01.2017 10:30:31
60
2
2
18970
01.01.2017 10:40:00
629
3
3
18970
01.01.2017 10:50:12
1241
4
5
754
346456
01.01.2017 10:23:45
105
5
4
789
6586
01.01.2017 12:34:45
60
6
6
4332
234567
01.01.2017 10:13:40
70
7
7
234567
01.01.2017 10:26:46
856