56,679
社区成员
发帖
与我相关
我的任务
分享
select * from t3
right join (
SELECT no,max(time) time FROM t3
group by no
order by no asc
)tt
on t3.no = tt.no and t3.time = tt.time
我是这么弄的select *
from
(
select *,
row_number() over(partition by no order by time desc) rn
from t3
)t
where rn = 1
mysql> create table t3
-> (
-> id int,
-> no int,
-> value int,
-> time datetime
-> );
Query OK, 0 rows affected (4.97 sec)
mysql>
mysql> insert into t3
-> select 1, 1, 1, '2016-02-26 10:10:10' union all
-> select 2, 2, 5, '2016-02-26 10:11:10' union all
-> select 3, 1, 10, '2016-02-26 10:12:10' union all
-> select 4, 1, 11, '2016-02-26 10:13:10' union all
-> select 5, 2, 8, '2016-02-26 10:14:10' union all
-> select 6, 1, 7, '2016-02-26 10:17:10' union all
-> select 7, 1, 3, '2016-02-26 10:18:10' union all
-> select 8, 3, 5, '2016-02-26 10:19:10' union all
-> select 9, 3, 1, '2016-02-26 10:20:10';
Query OK, 9 rows affected (1.00 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+------+------+-------+---------------------+
| id | no | value | time |
+------+------+-------+---------------------+
| 1 | 1 | 1 | 2016-02-26 10:10:10 |
| 2 | 2 | 5 | 2016-02-26 10:11:10 |
| 3 | 1 | 10 | 2016-02-26 10:12:10 |
| 4 | 1 | 11 | 2016-02-26 10:13:10 |
| 5 | 2 | 8 | 2016-02-26 10:14:10 |
| 6 | 1 | 7 | 2016-02-26 10:17:10 |
| 7 | 1 | 3 | 2016-02-26 10:18:10 |
| 8 | 3 | 5 | 2016-02-26 10:19:10 |
| 9 | 3 | 1 | 2016-02-26 10:20:10 |
+------+------+-------+---------------------+
9 rows in set (0.01 sec)
mysql> select id,no,value,time
-> from
-> (
-> select *
-> from t3
-> order by no,time desc
-> ) t
-> group by no;
+------+------+-------+---------------------+
| id | no | value | time |
+------+------+-------+---------------------+
| 7 | 1 | 3 | 2016-02-26 10:18:10 |
| 5 | 2 | 8 | 2016-02-26 10:14:10 |
| 9 | 3 | 1 | 2016-02-26 10:20:10 |
+------+------+-------+---------------------+
3 rows in set (0.11 sec)
select id,no,value,time
from
(
select *
from t3
order by no,time desc
) t
group by no;