欄位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
求大神幫助， 謝謝！！！
uj5u.com熱心網友回復：
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 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>
uj5u.com熱心網友回復：
這樣查詢少量資料可以， 單表如果有幾十萬的資料， 查詢就很慢， 連表查詢如何做優化？轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/79503.html
標籤：MySQL
上一篇：資料核對問題