mysql> use mytest;
Database changed
mysql> select * from table2;
+-------+------+----------+
| t | type | time |
+-------+------+----------+
| 10000 | 0 | 08:43:00 |
| 10000 | 0 | 09:44:00 |
| 10001 | 1 | 09:22:00 |
| 10001 | 1 | 09:23:00 |
| 10001 | 1 | 09:24:00 |
| 10001 | 0 | 10:00:00 |
| 10001 | 0 | 11:00:00 |
+-------+------+----------+
7 rows in set (0.00 sec)怎么样select 的结果是显示type=0 的 每个 t值 时间最大的值。
10000 0 09:44:00
10001 0 11:00:00
Database changed
mysql> select * from table2;
+-------+------+----------+
| t | type | time |
+-------+------+----------+
| 10000 | 0 | 08:43:00 |
| 10000 | 0 | 09:44:00 |
| 10001 | 1 | 09:22:00 |
| 10001 | 1 | 09:23:00 |
| 10001 | 1 | 09:24:00 |
| 10001 | 0 | 10:00:00 |
| 10001 | 0 | 11:00:00 |
+-------+------+----------+
7 rows in set (0.00 sec)怎么样select 的结果是显示type=0 的 每个 t值 时间最大的值。
10000 0 09:44:00
10001 0 11:00:00
+-------+------+----------+
| t | type | time |
+-------+------+----------+
| 10000 | 0 | 08:43:00 |
| 10000 | 0 | 09:44:00 |
| 10001 | 1 | 09:22:00 |
| 10001 | 1 | 09:23:00 |
| 10001 | 1 | 09:24:00 |
| 10001 | 0 | 10:00:00 |
| 10001 | 0 | 11:00:00 |
+-------+------+----------+
7 rows in set (0.00 sec)mysql> select *
-> from table2 a
-> where type=0
-> and not exists (select 1 from table2 where t=a.t and type=0 and `time`>a.time);
+-------+------+----------+
| t | type | time |
+-------+------+----------+
| 10000 | 0 | 09:44:00 |
| 10001 | 0 | 11:00:00 |
+-------+------+----------+
2 rows in set (0.00 sec)mysql>
(SELECT t r,MAX(time) Y FROM table2 GROUP BY t) a,
(SELECT t r,type b,MAX(time) Y FROM a GROUP BY t,type) b
WHERE a.r=b.r
AND a.y=b.y;