Field is a field b
A 01:00:00
A 02:00:00
A 03:00:00
B 04:00:00
B 05:00:00
B 06:00:00
C 07:00:00
C 08:00:00
C 09:00:00
A 10:00:00
A 11:00:00
A 12:00:00

Hope to get the result is
A 01:00:00 03:00:00
B 03:00:00 06:00:00
C 07:00:00 09:00:00
A 10:00:00 12:00:00

Seek help from a great god, thank you!!!!!!

CodePudding user response:

 mysql> Select * from cdh1213; 
+ -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 
| | a | b 
+ -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 
| a | 01:00:00 | 
| a | 02:00:00 | 
| a | 03:00:00 | 
| | b 04:00:00 | 
| | b 05:00:00 | 
| | b 06:00:00 | 
| | c 07:00:00 | 
| | c 08:00:00 | 
| | c 09:00:00 | 
| a | 10:00:00 | 
| a | 11:00:00 | 
| a | 12:00:00 | 
+ -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 
12 rows in the set (0.01 SEC) 

Mysql> Select *, (select Max (b) the from cdh1213 v where a=t.a and b> T.b and not the exists (select 1 from cdh1213 where b between t.b and what exactly the and a! As e=t.a)) 
-> The from cdh1213 t 
-> Where a!=(select a from cdh1213 where b-> Or not the exists (select 1 from cdh1213 where b+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 
| | e | a | b 
+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 
| a | 01:00:00 | 03:00:00 | 
| | b 04:00:00 | 06:00:00 | 
| | c 07:00:00 | 09:00:00 | 
| a | 10:00:00 | 12:00:00 | 
+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 
4 rows in the set (0.00 SEC) 

Mysql> 

CodePudding user response:

Such query can a small amount of data, single table if there are hundreds of thousands of data, the query is very slow, even the table query optimization how to do?