字段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希望得到的结果是
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求大神帮助, 谢谢!!!
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希望得到的结果是
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求大神帮助, 谢谢!!!
+------+----------+
| 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 set (0.01 sec)mysql> select *, (select max(b) from cdh1213 v where a=t.a and b>t.b and not exists (select 1 from cdh1213 where b between t.b and v.b and a!=t.a) ) as e
-> from cdh1213 t
-> where a != (select a from cdh1213 where b<t.b order by b desc limit 1)
-> or not exists (select 1 from cdh1213 where b<t.b );
+------+----------+----------+
| a | b | e |
+------+----------+----------+
| 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 set (0.00 sec)mysql>